 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.

# Elapsed Days as Years, Months and Days by Allen Wyatt
(last updated November 6, 2020)

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.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

AutoFill with Random Numbers

When entering data into a worksheet, you may have a need to fill a range of cells with a group of random numbers. This ...

Discover More

Searching for Special Hyphens

Word allows you to use a couple of different types of hyphens in your document, each with a different purpose. If you ...

Discover More

No Ribbon on New Documents

The way that you work with commands in Word is primarily through the ribbon. What do you do if the ribbon disappears when ...

Discover More Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

##### More ExcelTips (ribbon)

Determining If a Year is a Leap Year

Need to figure out if a given year is a leap year? It's not as easy as you think! This tip provides a plethora of ways ...

Discover More

Converting an Unsupported Date Format

Excel makes it easy to import information created in other programs. Converting the imported data into something you can ...

Discover More

Want to add an ordinal suffix to a number, as in 2nd, 3rd, or 4th? Excel doesn't provide a way to do it automatically, ...

Discover More
##### Subscribe

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

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 1 + 0?

2016-06-10 11:45:20

Willy Vanhaelen

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.

2016-06-08 05:47:40

Pete

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

2016-06-08 05:41:19

Pete

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

2016-06-08 04:27:24

Mark

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

2016-06-07 15:32:19

Robert Lohman

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

2016-06-07 12:19:17

L Marquez

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

2016-06-07 07:29:04

Christos Constantinou

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

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

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

2014-07-11 09:09:24

Michael (Micky) Avidan

@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

2014-07-11 07:17:18

Bryan

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

2014-07-10 12:19:52

Cathy

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

2013-09-09 16:00:30

Bryan

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

2013-09-09 13:05:35

Bryan

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.

2013-09-09 12:41:33

Bryan

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

##### This Site

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.