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 November 16, 2017)

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

AutoFormat within Form Fields

When entering information into protected form fields, Word doesn't apply the formatting inherent in the AutoFormat As You ...

Discover More

Changing Elements in Lots of Charts at One Time

Got a bunch of charts that you need to make formatting changes in? You can use a macro (or two) to apply the formatting ...

Discover More

Spell Checking Forms

Word may be used to create protected forms that limit where the user may input data. Normally spell checking is disabled ...

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)

Working with Minutes

For many Excel users—particularly beginners—working with elapsed time can be bewildering. This tip explains ...

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

Weekdays in a Month

Want to find out how many of a particular weekday occur within a given month? Here's how you can find the desired ...

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.

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 8 + 5?

2016-03-03 13:51:49

Lisa O\'Reilly CE

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.

2014-12-17 13:14:12

Willy Vanhaelen

@Micky: I totally agree with you. Such "jumbo" formulas are highly unmanageable and can mostly be replaced by relatively simple UDF's

2014-12-16 07:02:36

Michael (Micky) Avidan

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)
ISRAEL

2014-12-16 02:53:09

Ian Goodwin

Awesome formulae! Well done.

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