Excel - Conversion days >> years, months, days

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.

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)]

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.

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!!!!

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!"

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?

the results differ from the 60th day

For Myta, we have 1 month and 30 days

And Mike 2 months 0 days

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)]

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