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 June 7, 2016)

15

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

Checking Your Data File

When you get ready to merge a document with a data source, you'll want to make sure that everything is "as expected" before ...

Discover More

Every Second Tuesday

Need a way to enter dates from every second Tuesday (or some other regular interval)? Excel makes it easy, providing several ...

Discover More

Indenting Cell Contents

Excel allows you to apply several types of alignments to cells. One type of alignment allows you to indent cell contents from ...

Discover More

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!

More ExcelTips (ribbon)

Determining If a Date is between Other Dates

Need to figure out if one date is between two other dates? There are a wide variety of formulaic approaches you could use to ...

Discover More

Converting Time Notation to Decimal Notation

Want 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

Pulling All Fridays

It can be handy to know when specific weekdays occur within a range of dates. Figuring out this information, using formulas, ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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}] in your comment text. You’ll be prompted to upload your image when you submit the comment. 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 0 + 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:
=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.

Newest Tips
Subscribe

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.