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

Written by Allen Wyatt (last updated November 6, 2020)
This tip applies to Excel 2007, 2010, and 2013


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:


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:

DATEDIF(E7,F7,"y")&" year, ",DATEDIF(E7,F7,"y")&" years, "))
DATEDIF(E7,F7,"ym")&" month, ",DATEDIF(E7,F7,"ym")&" months, "))
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(AND(DATEDIF(E7,F7,"y")<>0,DATEDIF(E7,F7,"ym")<>0),", ","")
DATEDIF(E7,F7,"ym")&" month",DATEDIF(E7,F7,"ym")&" months"))
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. ...


Cross-referencing to an Automatic Number

Word allows you to add automatic numbering to different elements of your document, such as to headings. If you want to ...

Discover More

Using Older Spelling and Grammar Checking

If you prefer the older spelling and grammar checking process used in Word, then you will find this tip helpful. It only ...

Discover More

Last Non-Zero Value in a Row

If you have a lot of values in a single row, you might want to pull the last non-zero value from that row. There are a ...

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)

Tombstone Date Math

Doing math with dates is easy in Excel. Doing math with old dates, such as those you routinely encounter in genealogy, is ...

Discover More

Calculating Dates for Thanksgiving

Ever wonder how to calculate the date for Thanksgiving in the United States? In this tip you discover not only that, but ...

Discover More

Calculating a Sum for a Range of Dates

If you use Excel to track information based on dates, you may wonder how to get a sum for only certain dates that you ...

Discover More

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

View most recent newsletter.


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 one more than 7?

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

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


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


L Marquez

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

DATEDIF(N6,AC6,"y")&" year",DATEDIF(N6,AC6,"y")&" years"))
&IF(AND(DATEDIF(N6,AC6,"y")<>0,DATEDIF(N6,AC6,"ym")<>0),", ","")
DATEDIF(N6,AC6,"ym")&" month",DATEDIF(N6,AC6,"ym")&" months"))
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


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?



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
It's OK thanks, I've managed to figure it out

Pete 26 Mar 2015, 06:37
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.

2015-03-26 07:05:09


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

2015-03-26 06:37:10


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.
If you want to present a positive duration, no matter the order of the two dates - then use:
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)

2014-07-11 07:17:18


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


If you want to return the negative value use this:


2014-07-10 12:19:52


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


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


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


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.

Newest Tips

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

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.