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.
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:
=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.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
When you enter a date into a cell and you omit the year, Excel helpfully adds the current year to the date. If you want ...
Discover MoreWhen you have a huge amount of daily data to analyze, you may want to calculate an average of values for any given date ...
Discover MoreNeed 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 MoreFREE 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
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).
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 © 2024 Sharon Parq Associates, Inc.
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:
Comments