Elapsed Days as Years, Months and Days

When I read this tip I got the impression that this was an attempt to get in the Guinness Book of Records for the lengthiest Excel formula.

I have no problem with i.e. "0 year(s), 5 month(s), 1 day(s)". Then the formula is still manageable:
=DATEDIF(E7,F7,"y") & " year(s), " & DATEDIF(E7,F7,"ym")& " month(s), " & DATEDIF(E7,F7,"md") & " day(s)"
"0 year(s)" is perhaps superfluous but it has the benefit of being crystal clear.

If you want the whole bunch of refinements then this UDF (user defined function) will do the job.

Function elaps(a As Date, b As Date) As String
Dim d As Integer, m As Integer, y As Integer, s As String
y = DateDiff("yyyy", a, b): a = DateAdd("yyyy", y, a)
m = DateDiff("m", a, b): a = DateAdd("m", m, a)
d = DateDiff("d", a, b)
If y Then s = y & Left(" years", 6 + (y = 1))
If m Then s = s & IIf(y, ", ", "") & m & Left(" months", 7 + (m = 1))
If d Then s = s & IIf(y Or m, ", ", "") & d & Left(" days", 5 + (d = 1))
elaps = s
End Function

Once the UDF in place you have the benefit you only have to enter this 13 characters formula:
=elaps(E7,F7)
Instead of the 486 characters formula in this tip.

You can also enter the 2 dates directly in the formula instead of referencing 2 cells:
=elaps("1/1/2012","9/9/2013")

Even if you want to stick with the formulas, you can shorten them a lot:

The "year" section of the third formula:
=IF(DATEDIF(E7,F7,"y")=1,DATEDIF(E7,F7,"y")&" year, ", DATEDIF(E7,F7,"y")&" years, ")
can be replaced with:
=DATEDIF(E7,F7,"y")&" year"&IF(DATEDIF(E7,F7,"y")=1,"","s")&","
You can apply this also to the "month" and "day" part and in the 2 last formulas as well.

Letty, if you want to use the formula just copy and paste it then go back and manually change the two cell addresses

L Marquez

This is a copy of what I use, in this particular case N6 is the start date and AC6 =TODAY()

=IF(DATEDIF(N6,AC6,"y")=0,"",IF(DATEDIF(N6,AC6,"y")=1,
DATEDIF(N6,AC6,"y")&" year",DATEDIF(N6,AC6,"y")&" years"))
&IF(AND(DATEDIF(N6,AC6,"y")<>0,DATEDIF(N6,AC6,"ym")<>0),", ","")
&IF(DATEDIF(N6,AC6,"ym")=0,"",IF(DATEDIF(N6,AC6,"ym")=1,
DATEDIF(N6,AC6,"ym")&" month",DATEDIF(N6,AC6,"ym")&" months"))
&IF(AND(OR(DATEDIF(N6,AC6,"y")<>0,DATEDIF(N6,AC6,"ym")<>0),
DATEDIF(N6,AC6,"md")<>0)," & ","")&IF(DATEDIF(N6,AC6,"md")=0,"",
IF(DATEDIF(N6,AC6,"md")=1,DATEDIF(N6,AC6,"md")&" day",
DATEDIF(N6,AC6,"md")&" days"))

Thanks Bryan for the excellent formulas. I work in school calendars I would like to ignore weekends and school holidays and calculate for 190 day year (180 days for colleges. Is this possible to adapt your formula to calculate to these requirements?

Regards,

Mark

I have inadvertently turned off the use of the ALT key in Excel 2010. It no longer will initialize the screen tips in the tabs. The forward slash does the job but I would like to know if there is a way to turn back on the ALT key to show screen tips. (Makes no difference if the Lotus compatibility in on or off). Driving me crazy- thank you

Pete or Bryon--could you explain how did you 'manage' I have a need for this formula. thank you. Letty


Pete 26 Mar 2015, 07:05
Bryon
It's OK thanks, I've managed to figure it out


Pete 26 Mar 2015, 06:37
Bryan
Many thanks for your 'DATEDIF' formula which I am now using.
Just to complicate it a bit - How can I use an ampersand after month or months instead of a comma?
So as to read: 1 year, 2 months & 3 days

Why do 17 (or even 12) calls to a function when you only need 2???
Use intermediate cells to get the results for year(s), month(s) and day(s)
and then do the logic for how to properly display those values.
simple.
faster.
:)

Bryon
It's OK thanks, I've managed to figure it out

Bryan
Many thanks for your 'DATEDIF' formula which I am now using.
Just to complicate it a bit - How can I use an ampersand after month or months instead of a comma?
So as to read: 1 year, 2 months & 3 days

@Cathy,
For only days duration - forget toy don't need the DATEDIF function.
The following, simple, formula will provide a Positive or Negative duration of days.
=AC27-AF27
If you want to present a positive duration, no matter the order of the two dates - then use:
=ABS(AC27-AF27)
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

Cathy, you could try the IFERROR formula. If you want to return a 0 for any response where days < 1 use this:

=IFERROR(DATEDIF(AF27,AC27, "D"),0)

If you want to return the negative value use this:

=IFERROR( DATEDIF(AF27,AC27, "D"), -DATEDIF(AC27,AF27, "D") )

HI, I have a basic dateif formula below but if the date in AF27 is later than the date in AC27 it comes back with #NUM!
is there a way to change the formula so that if the days are less than 1 it comes back with either a negative number or a zero?


= DATEDIF(AF27,AC27, "D")

Turns out I was able to condense some more:

=IF(Years,Years&" year"&IF(Years>1,"s",""),"")
&IF(Years*Months,", ","")
&IF(Months,Months&" month"&IF(Months>1,"s",""),"")
&IF((Years+Months)*Days,", ","")
&IF(Days,Days&" day"&IF(Days>1,"s",""),"")

has 195 characters (60% reduction) and

=CHOOSE(MIN(Years,2)+1,"","1 year",Years&" years")
&IF(Years*Months,", ","")
&CHOOSE(MIN(Months,2)+1,"","1 month",Months&" months")
&IF((Years+Months)*Days,", ","")
&CHOOSE(MIN(Days,2)+1,"","1 day",Days&" days")

has 207 characters (57% reduction). I took advantage of the fact that true/false statements can be numbers (0=false, anything else = true).

Oops, realized there was an error in the second line of each formula. Change it to

=IF(DATEDIF(E7,F7,"y")*DATEDIF(E7,F7,"ym")>0,", ","")

in the first formula or

=IF(Years*Months>0,", ","")

in the latter two. This reduces the formulas by 1 additional DateDif call and 33 and 18 characters, respectively.

When trying to figure out a better way to do formula 3, I found an error. In both places where you have "year" in line two, it needs to be " year". Otherwise you get "2years" instead of "2 years".

I'm no expert, but here's my attempt to both shorten and clarify the formula:

=IF(DATEDIF(A9,A10,"y")=0,"",DATEDIF(A9,A10,"y")&" year"&IF(DATEDIF(A9,A10,"y")>1,"s",""))
&IF(AND(DATEDIF(A9,A10,"y")>0,OR(DATEDIF(A9,A10,"ym")>0,DATEDIF(A9,A10,"md")>0)),", ","")
&IF(DATEDIF(A9,A10,"ym")=0,"",DATEDIF(A9,A10,"ym")&" month"&IF(DATEDIF(A9,A10,"ym")>1,"s",""))
&IF(AND(DATEDIF(A9,A10,"y")+DATEDIF(A9,A10,"ym")>0,DATEDIF(A9,A10,"md")>0),", ","")
&IF(DATEDIF(A9,A10,"md")=0,"",DATEDIF(A9,A10,"md")&" day"&IF(DATEDIF(A9,A10,"md")>1,"s",""))

I'm sure it can be shortened further, but for me this is the fine balance between short and clear. It reduces the length by 7% (483 characters to 448 characters) and reduces the number of DateDif calls by 12% (17 to 15). You can further clarify the formula (and reduce its lenght) by creating named formulas (these are named ranges that have a formula as the reference instead of a range):

=IF(Years=0,"",Years&" year"&IF(Years>1,"s",""))
&IF(AND(Years>0,OR(Months>0,Days>0)),", ","")
&IF(Months=0,"",Months&" month"&IF(Months>1,"s",""))
&IF(AND(Years+Months>0,Days>0),", ","")
&IF(Days=0,"",Days&" day"&IF(Days>1,"s",""))

This reduces the formula by 53% (227 characters). If you want to pay a small bit of clarity and a few extra characters of length, you can decrease the number of DateDif calls:

=CHOOSE(MIN(Years,2)+1,"","1 year",Years&" years")
&IF(AND(Years>0,OR(Months>0,Days>0)),", ","")
&CHOOSE(MIN(Months,2)+1,"","1 month",Months&" months")
&IF(AND(Years+Months>0,Days>0),", ","")
&CHOOSE(MIN(Days,2)+1,"","1 day",Days&" days")

That is only 7 characters longer, but reduces the DateDiff calls to 12 (29% reduction).