View original (French)

Excel - Conversion days >> years, months, days

Solved
Tehani_t Posted messages 245 Status Member -  
 Xfiles -
Hello,

Hello, is it possible to convert a number of days into the number of YEARS, number of MONTHS, and number of DAYS?

Example: 539 days = 1 year, 5 months and 23 days

Thank you

Configuration: Windows 2000 / Firefox 2.0.0.17

9 answers

Answer 1 / 9
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
 
Hello,

the conversion of a number of days into years, months, and remaining days is only approximate because we cannot rely on any starting date to take into account the number of leap years and the number of days in each month.
This formula provides a fairly accurate calculation

=INT(A1/365) & " year(s) " & INT(MOD(A1,365)/30) & " month(s) " & MOD(A1,365)-(INT(MOD(A1,365)/30)*30) & " day(s)"

--
A+
Mike-31

A period of failure is a perfect moment to sow the seeds of knowledge.
19
Tehani_t Posted messages 245 Status Member 21
 
Sorry for the lack of precision.

In A1: start of availability
In A2: end of availability
In A3: availability duration in days
In A4: availability duration in days divided by 2

Is it possible to convert the duration in A4 into the number of years, months, and days, and obtain the corresponding date?
0
Mytå Posted messages 4246 Registration date   Status Contributor Last intervention   957
 
Re the forum

Thank you for putting values!

Mytå
0
Tehani_t Posted messages 245 Status Member 21
 
A1 : 01/01/2008
A2 : 23/06/2009
A3 : 539
We therefore obtain years, months, and days:

Myta's solution: 1 year 5 months 22 days
Mike's solution: 1 year 5 months 24 days
0
Margo
 
=DATEDIF(DATE(2001,1,1),DATE(2001,1,1) + AH22,"y") & " ans, " & DATEDIF(DATE(2001,1,1) + DATEDIF(DATE(2001,1,1),DATE(2001,1,1) + AH22,"y"),DATE(2001,1,1) + AH22,"m") & " mois, " & DATEDIF(DATE(2001,1,1) + DATEDIF(DATE(2001,1,1),DATE(2001,1,1) + AH22,"y"),DATE(2001,1,1) + AH22,"d") & " jours"
0
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
 
Hi,

you should start your own discussion and avoid reviving an old one

=INT(A4/12)
0
Answer 2 / 9
Mytå Posted messages 4246 Registration date   Status Contributor Last intervention   957
 
Hello forum

A bit far-fetched as a formula 

=DATEDIF(DATE(2000;0;0);DATE(2000;0;539);"Y") & " year(s), " & DATEDIF(DATE(2000;0;0);DATE(2000;0;539);"ym") & " month(s), and " & DATEDIF(DATE(2000;0;0);DATE(2000;0;539);"md") & " day(s)."


Mytå
--
Thank you for following up on your question, we are not robots...
Installed versions [MsProject 2003(EN), Excel 2003-2007(EN)]
7
Tehani_t Posted messages 245 Status Member 21
 
Thank you, Myta!! It works for this case, but if the duration is 1 day, the result is the following: 0 years, 0 months, and 0 days.
0
Tehani_t Posted messages 245 Status Member 21
 
Sorry Myta, I messed up!!!! It indeed gives 1 day.
0
Answer 3 / 9
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
 
Hi,

If you have a start date in A1 and an end date in A2, it changes the matter

to calculate the number of days between the two dates

=DATEDIF(A1,A2,"d")

to break down the difference between these two dates which will give you a result minus one day (principle of intervals)

=DATEDIF(A1,A2,"y")&" year(s), "&DATEDIF(A1,A2,"ym")&" month(s) and "&DATEDIF(A1,A2,"md")&" day(s)"

however, if the first day and the last day need to be counted, the formula that fits

=DATEDIF(A1,A2+1,"y")&" year(s), "&DATEDIF(A1,A2+1,"ym")&" month(s) and "&DATEDIF(A1,A2+1,"md")&" day(s)"

now if you want to break down from a number, the count will be only approximate unless we rely on a fictitious date like TODAY() to create a start or end date

--
A+
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.
7
Answer 4 / 9
Tehani_t Posted messages 245 Status Member 21
 
With Myta's solution, we have: for 365: 11 months and 30 days

With Mike's solution, we have: for 365: 1 year

In one case, we operate by months of 30 days and in the other by a complete year

Thank you all!!!!
2
Answer 5 / 9
Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 783
 
To confirm what Mike-31 says:

01/01/2010 + 539 days = 24/06/2011 (1 year, 5 months, and 23 days)
01/02/2010 + 539 days = 25/07/2011 (1 year, 5 months, and 24 days)
01/03/2010 + 539 days = 22/08/2011 (1 year, 5 months, and 21 days)
01/10/2010 + 539 days = 23/03/2012 (1 year, 5 months, and 22 days)

Best regards
--
Nicolas always says: "It's easy when you know the answer!"
0
Answer 6 / 9
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
 
Hello everyone
=DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&" months "&DATEDIF(A1,B1,"md")&" days"
this one gives, with all reservations, the results displayed by Patrice here
https://forums.commentcamarche.net/forum/affich-18965056-excel-conversion-nb-jour-an-mois-jour
with the date in A1 and in B1 =A1+539
cheers
--
Let's ask ourselves if we are not alone in understanding what we are explaining?
0
Mytå Posted messages 4246 Registration date   Status Contributor Last intervention   957
 
Re the forum

I was stuck on his notion of 539 days, but the formulas given are in the file.

Mytå
0
Answer 7 / 9
Tehani_t Posted messages 245 Status Member 21
 
the results differ from the 60th day

For Myta, we have 1 month and 30 days

And Mike 2 months 0 days
-1
Answer 8 / 9
Mytå Posted messages 4246 Registration date   Status Contributor Last intervention   957
 
Re the forum

Read on www.vbfrance.com:
“If there is one thing that cannot be transformed into another,
it is indeed the number of days into years, months, and days!
The notion of year and month is indeed calendar-based and not linear.
Thus, 28 days can make one month here and only 28 days there...
31 days can make one month and 3 days, or one month (depending on the case)! ”

Tehani, 3 formulas on the file

The file: Convertir_Jours.xls

Mytå
Thank you for following up on your question, we are not robots...
Installed versions [MsProject 2003(FR), Excel 2003-2007(FR)]
-1
Tehani_t Posted messages 245 Status Member 21
 
You're welcome!
0
Answer 9 / 9
poulain
 
yes it is possible to convert:

function:
=DATEDIF(A1,A2,"y") converts to years
=DATEDIF(A1,A2,"m") converts to months
=DATEDIF(A1,A2,"d") converts to days

example A1: 10/12/200
A2: 06/05/2010
plan a column for each result
-1
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
 
Hi,

I don't see the point of reviving an old discussion without bringing in new elements when its status is resolved.

See you!
0