Calculate the difference of total number of years, months and days together between starting date and ending date.

[VDS]

Dear All experts,

Kindly provide me a function to calculate the difference of total number of years, months and days together in Excel. I have attached the sample files for your study. I know that the date function and datedif function. Here starting date is 18.01.2018 and ending date is 15.04.2022. Result would be something like 4 years, 3 months and 27 days.

VDS

 

[ETAF]

how about
for years
=DATEDIF(E4, F4, "y")
for months
=DATEDIF(E4, F4, "YM")
for days
=F4-DATE(YEAR(F4),MONTH(F4),1)

then you can concatenate

="years "&H4&" months "&I4&" days "&J4

OR

="years "&DATEDIF(E4, F4, "y")&" months "&DATEDIF(E4, F4, "YM")&" days "&F4-DATE(YEAR(F4),MONTH(F4),1)

 

[VDS]

Thanks a lot . It is ok

 

[vletm]

VDS​

or
=YEAR(F4-E4)-1900 & " years, " & MONTH(F4-E4) & " months and " & DAY(F4-E4) & " days"

 

[ETAF]

my version is wrong ....
4 years
2 months
28 days
is that the result you want

Excel formula: Get days, months, and years between dates

Excel has very useful date calculation functions. This article will show you how to get days, months, and years between dates in Excel.

www.got-it.ai

=IF(DATEDIF(E4,F4,"y"),DATEDIF(E4,F4,"y")&" years","")&IF(DATEDIF(E4,F4,"ym"),", "&DATEDIF(E4,F4,"ym")&" months","")&IF(DATEDIF(E4,F4,"md"),", "&DATEDIF(E4,F4,"md")&" days","")

see D1 in attached


mine gives
the wrong answer

Not sure this is correct as it gives 3 months , which would be 18th april
=YEAR(F4-E4)-1900 & " years, " & MONTH(F4-E4) & " months and " & DAY(F4-E4) & " days"

 

[Peter Bartholomew]

Since the month is a varying unit of time the formulas appear to be open to some interpretation. A number are included here, including my 'packaging' of one of the formulas as a custom Lambda function.

 

[ETAF]

including my 'packaging' of one of the formulas as a custom Lambda function.

hows that done ?

 

[Peter Bartholomew]

This might be the most reliable? It wins no awards for brevity though!

= LAMBDA(start,end,[numeric?],
    LET(
      entireYears,  DATEDIF(start, end, "y"),
      anniversary,  EDATE(start, 12*entireYears),
      entiremonths, DATEDIF(anniversary, end, "m"),
      prevDayOfMth, EDATE(anniversary, entiremonths),
      days,         end - prevDayOfMth,
      diffArray,    VSTACK(entireYears, entiremonths, days),
      units,        {" years, ";" months and ";" days."},
      IF(numeric?,  diffArray, CONCAT(HSTACK(diffArray, units)))
    )
  )(startDate, endDate, 0)

@ETAF
You need Excel 365 for LAMBDA to be present. If you have that, one can define a Name to refer to a formula such as the above [Note: all Defined Names refer to formulas that are only evaluated when the Name is referenced]. In this case the formula starts with LAMBDA and ends with the closing parenthesis that precedes the final parameter string "(startDate, endDate, 0)". By using the defined name followed by the parameter string one has the conventional syntax for a function call.

= DATEDIFFλ(startDate, endDate)

A good feature of programming Excel this way is that editing the Lambda function (Microsoft offers a special formula editor for that purpose), one can correct occurrences of the function throughout the workbook without needing to hunt them out to make corrections individually. I hope this helps and is of interest.

 

[ETAF]

thanks,
i have 365 latest version 16.78 and the formula worked on the sheet you posted , i guess if the feature was missing i would get a value error
i'll have to look more into that

 

[vletm]

ETAF​

You wrote something about below formula.

Anyway, it give as written in #1 reply.
Here starting date is 18.01.2018 and ending date is 15.04.2022.
Result would be something like 4 years, 3 months and 27 days.

 

[Peter Bartholomew]

@ETAF If you are going to go exploring, it would be worth downloading Excel Labs from the App Store. The AFE allows you to examine and edit Lambda functions far more effectively than Name Manager. It is also perfectly possible to develop LET and LAMBDA function on the sheet but the formula bar needs to be set with multiple lines showing. To insert a line feed into a formula one uses Alt/Enter.

 

[Peter Bartholomew]

@vletm
I suspect you are hitting the problem that different groups of months are spanned by intervals of different lengths. So starting with January 1900 will not necessarily give the correct count of months (it depends on whether they are long months or short months).

 

[vletm]

Peter Bartholomew​

I offered expected result ... with something like- it can be different than correct result.

I've a feeling that Excel knows number of Months in any year, but number of leap years could have some challenge in this kind of case.
If gap is 1527 days - how many Years, Month & Days it is?
I'll start counting like with calendar from 1st of January ... year 0.

Other minor challenge could be - how to read result?
Did You check what Your formula give if cell F4 is 20.01.2018?
Mine gives ... hmm? ... 0 year, 1 months and 2 days. (1st month is January ... 'Calendar way so show this')
Someway, it's Okay - if can figure it as Date's work.
But it could be smoother to give 0 year, 0 months and 2 days ( two days ).

I won't call these Your named problem ... I would call these like challenges.

Or
Did VDS expect something like age?

'My way'...
If I would have tried to solve this 'my way' ( other than formula-way )
then I'll do it eg with function ... check cell J5.
It gives different result that expected one
and it shows values (y, m or d) if those are over zero

After UDF...
if modify 'my' G-column formula's Month-part.
it would give more nearer results with UDF-solution.
But still there seems to know - how to read results.

Both samples could work with many Excel-versions ... if Macros are enabled.

 

[davidbazesz]

i have 365 latest version 16.78 and the formula worked on the sheet you posted , i guess if the feature was missing i would get a value error

 

[Danilão]

Try this Code :

=IF(ISBLANK(E4),"",IFERROR(DATEDIF(E4,F4,"y")&" Years "&DATEDIF(E4,F4,"ym")&" Months "&DATEDIF(E4,F4,"md")&" Days",""))