**Please Note: **
This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), *this tip may not work for you*. For a version of this tip written specifically for earlier versions of Excel, click here: Elapsed Days as Years, Months and Days.

If you are using Excel to track information about projects, you may want to know the duration of a given project in years, months, and days. If you have the starting date and the ending date for each project, you can use the DATEDIF worksheet function to return the information in the desired manner.

For instance, let's assume that you have a starting date in cell E7 and the ending date in cell F7. You can calculate the difference between the two dates with this very simple use of DATEDIF:

=DATEDIF(E7,F7,"d")

This function returns the number of days between the two dates, provided the date in E7 is less than or equal to the date in F7. The third argument, "d", causes DATEDIF to return its result in days. You can also specify months ("m") and years ('y"). For the purposes of this example, however, there are several other arguments that are particularly helpful: months excluding years ("ym"), days excluding years ("yd"), and days excluding months and years ("md").

Using these different arguments, you can concoct a formula that will return an answer indicating the elapsed days as years, months and days. (Because of the length of the formulas in this tip, I've broken them into separate lines to make them a bit easier to read. This is a single formula, however, and should be entered as such into Excel.)

=DATEDIF(E7,F7,"y") & " years, " & DATEDIF(E7,F7,"ym") & " months, " & DATEDIF(E7,F7,"md") & " days "

Note that this formula will always return plural units, as in years, months, and days. For those who want to be grammatically correct and provide singular units when it is called for, the following formula will do the trick:

=IF(DATEDIF(E7,F7,"y")=1,DATEDIF(E7,F7,"y")&" year, ", DATEDIF(E7,F7,"y")&" years, ")&IF(DATEDIF(E7,F7,"ym")=1, DATEDIF(E7,F7,"ym") &" month, ",DATEDIF(E7,F7,"ym") &" months, ")&IF(DATEDIF(E7,F7,"md")=1,DATEDIF(E7,F7,"md") &" day",DATEDIF(E7,F7,"md")&" days")

This works in all instances except when either years, months, or days is zero. To get rid of the proper unit when it is zero requires an even longer formula:

=IF(DATEDIF(E7,F7,"y")=0,"",IF(DATEDIF(E7,F7,"y")=1, DATEDIF(E7,F7,"y")&" year, ",DATEDIF(E7,F7,"y")&" years, ")) &IF(DATEDIF(E7,F7,"ym")=0,"",IF(DATEDIF(E7,F7,"ym")=1, DATEDIF(E7,F7,"ym")&" month, ",DATEDIF(E7,F7,"ym")&" months, ")) &IF(DATEDIF(E7,F7,"md")=0,"",IF(DATEDIF(E7,F7,"md")=0, DATEDIF(E7,F7,"md")&" day ",DATEDIF(E7,F7,"md")&" days"))

This formula is getting quite long, but it will return only those units for which there is a value. Thus, instead of returning "0 years, 2 months, 1 day", it will return "2 months, 1 day."

Even this is not a perfect formula, as it will still display the commas between entries in some situations where they are not warranted. The following megaformula should fix plurals and commas and get rid of zero entries.

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

*ExcelTips* is your source for cost-effective Microsoft Excel training.
This tip (11360) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: **Elapsed Days as Years, Months and Days**.

**Professional Development Guidance!** Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out *Professional Excel Development* today!

If you import information generated on a UNIX system, you may need to figure out how to change the date/time stamps to ...

Discover MoreCalculating an age is a common task when working with dates. If you want to figure out the number of years and months ...

Discover MoreWant to convert an elapsed time, such as 8:37, to a decimal time, such as 8.62? If you know how Excel stores times ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2016-06-10 11:45:20

Willy Vanhaelen

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.

2016-06-08 05:47:40

Pete

2016-06-08 05:41:19

Pete

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

2016-06-08 04:27:24

Mark

Regards,

Mark

2016-06-07 15:32:19

Robert Lohman

2016-06-07 12:19:17

L Marquez

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

2016-06-07 07:29:04

Christos Constantinou

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.

:)

2015-03-26 07:05:09

Pete

Bryon

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

2015-03-26 06:37:10

Pete

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

2014-07-11 09:09:24

Michael (Micky) Avidan

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

2014-07-11 07:17:18

Bryan

=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") )

2014-07-10 12:19:52

Cathy

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

2013-09-09 16:00:30

Bryan

=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).

2013-09-09 13:05:35

Bryan

=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.

2013-09-09 12:41:33

Bryan

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

Got a version of Excel that uses the
ribbon interface (Excel 2007 or later)?
**This site is for you!** If you
use an earlier version of Excel, visit
our *ExcelTips* site focusing on the menu interface.

**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Copyright © 2020 Sharon Parq Associates, Inc.

## Comments