Excel.Tips.Net ExcelTips (Menu Interface)

Elapsed Days as Years, Months and Days

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later 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:


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 (2184) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Elapsed Days as Years, Months and Days.

Related Tips:

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!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

Lisa O'Reilly CE    03 Mar 2016, 13:51
Thank you.
This was exactly what I was looking to confirm. Your explanation was clear and I replicated the details in my own work.
Much appreciated.
Willy Vanhaelen    17 Dec 2014, 13:14
@Micky: I totally agree with you. Such "jumbo" formulas are highly unmanageable and can mostly be replaced by relatively simple UDF's
Michael (Micky) Avidan    16 Dec 2014, 07:02
I will just put it here...
I'm known as a very "sensitive Excelist" and therefore huge formulas "make me sick".
There is no reason to DUPLICATE such a formula because using the term: month(s) cover single & plural.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
Ian Goodwin    16 Dec 2014, 02:53
Awesome formulae! Well done.

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.