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)

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

Quickly Displaying the Page Setup Dialog Box

The Page Setup dialog box is indispensable in setting up the overall look of your document. You can display the dialog ...

Discover More

Removing Personal Information

As you create and work on your workbooks, Excel can include sensitive personal information with the data. If you want to ...

Discover More

Microsoft Word VBA Guidebook

Creating Word macros allows you to extend your productivity with Word. Visual Basic for Applications (VBA) is the ...

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!

End-of-Month Calculations

Don't want to use the EOMONTH function to figure out the end of a given month? Here are some other ideas for discovering ...

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

Excel allows you to perform quite a few operations using dates in your worksheet. Sometimes, however, the answer may not ...

Discover More
##### Subscribe

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

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 seven minus 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.