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.

Elapsed Days as Years, Months and Days

by Allen Wyatt
(last updated July 18, 2020)

2

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

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

Slow Excel Response Times

Many people use Excel on a notebook computer, so they can do work at the office and elsewhere. In some situations, Excel ...

Discover More

Jumping to the End of a Word

Using shortcut keys to navigate through your document is really handy. One navigation shortcut that Word doesn't provide ...

Discover More

Dynamic Data Based on Chart Changes

Change the data on which a chart is based and Excel obligingly updates the chart to reflect the change. What if you want ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (menu)

Parsing Non-Standard Date Formats

When you load data into Excel that was created in other programs, the formatting used for some types of data (such as ...

Discover More

Using Excel for Timing

Excel allows you to store times in a worksheet. If you want to use Excel to time certain events, there are a couple of ...

Discover More

Counting Dates in a Range

Excel makes working with a list of dates relatively easy. If you have a list of dates, you may need to know how many of ...

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. 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 5 - 2?

2020-07-19 01:30:16

Stephen Ditchfield

Hi Allen,
is there a way to alter this formula if there is both dd,mm,yyyy and or yyyy in E7

thank you
Stephen


2020-07-18 13:05:33

Trevor P. Dutt

As a recent subscriber I am enjoying Excel Tips (I use Excel 2002) but would p[oint out a small omission in the second line of the fourth formula above. To maintain the correct formatting of the result there should be a space between the quotation mark and the word 'year' and, similarly, before the word 'years'.
Best regards,
Trevor P. Dutt


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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.