FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
March 3, 2016
 
Greetings from The Excel Addict
Hi fellow Excel Addict,

T
hanks for joining me for another 'Excel in Minutes' tip. I received a lot of great feedback on my Sparklines tip last week.

"You made Sparklines very simple. I wonder why I didn't use it before. Awesome tip." - Ahmed N.

"For that being so simple, is a huge tool that I will be using daily now, and I am glad you pointed that out.  Thanks Francis!" - Paul G.

It's funny how easy it is to overlook things that are right in front of us day in and day out. Excel is so chock-a-block full of cool new ways to get things done faster, the possibilities seem almost endless.

Now I don't promise that every tip I share with you is going to 'rock your world'. However, I do believe that if you consistently read each tip, consider how you may be able to apply it to your work and then practice it, the ones that you do find helpful will save you a significant amount of time in improved productivity.

If you missed Tuesday's 'Excel in Seconds' newsletter, in it I showed how such a simple and common need to 'Copy Column Widths' isn't obvious in Excel. You can read that newsletter here.


In today's newsletter you will learn about "Excel's 'Secret' Function for Calculating Date Differences". I hope you find it helpful. Let me know how you will be putting it to use in your work.

Please feel free to share my newsletter with anyone you think could use a little Excel help.


Take care and keep on Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com


Francis Hayes (TheExcelAddict.com)




If you missed my last newsletter, you can click here to view it online.

 
TheExcelAddict.com Quote of the Day

"The only thing that holds you back from getting what you want
is paying attention to what you don’t want."
-- Abraham-Hicks --

 
If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

Excel's 'Secret' Function for Calculating Date Differences

Summary:
If you've ever needed a formula to calculate the number of months or years between two dates but couldn't figure out how to do it, I'm going to let you in on a little secret. It's called the DATEDIF function.

DATEDIF WARNING:
Thanks to Excel Addict subscriber Steve who reminded me (after I had published this tip) that DATEDIF has a bug in some versions of Excel. Please read this post by Rick Rothstein who advises against using DATEDIF for anything important.


Details:
Unfortunately, too many users don't understand how Excel handles dates and often end up typing an apostrophe in front of their dates to get them to display the way they want. That apostrophe turns a date into text rather than a number and precludes a very important part of Excel's capabilities, namely using dates in calculations.

Once you understand how dates work in Excel, very soon you will be asking "How can I figure out how many days/months/years there are between these dates?"


Calculate the Number of Days Between Two Dates
Calculating the number of days between two dates using Excel is pretty simple. You don't even need a function. Just use a simple formula to subtract the start date from the end date.

For example, if cell B1 contains 1-Jan-2016 and cell B2 contains 03-Mar-2016, you simply enter the formula =B2-B1 in cell B3 to get the number of days. At first the result may appear to be wrong. That's because Excel sometimes assumes you are entering another date and automatically applies a date format to the cell. You can easily correct that by changing the number format of that cell to General. Your result then should be 62.

Formula To Calculate Days Between Two Dates_in_Microsoft_Excel_2007_2010_2013_2016_365
You can use the DATEDIF function to calculate the number of days but why would you? A simple formula like the one above to subtract one date from the other is easier.

Calculating the number of months or years between two dates isn't as easy with a regular formula.

The DATEDIF Function
There's a 'secret' function in Excel that makes calculating the number of years and months between two dates easy. The function is called DATEDIF (i.e. date difference) and for some reason Microsoft has essentially hidden it. You won't even find it in the functions list.

Datedif Not In Functions List_in_Microsoft_Excel_2007_2010_2013_2016_365
The syntax for the DATEDIF function is
=DATEDIF(start_date, end_date, "interval").

Calculate the Number of Months Between Two Dates
Let's use the same dates as above to calculate the number of complete months ("m") between the start date and the end date. The formula we use is =DATEDIF(B1,B2,"m") where B1 is the start date and B2 is the end date. The result is 2.
 
Formula To Calculate Months Between Two Dates_in_Microsoft_Excel_2007_2010_2013_2016_365
Calculate the Number of Years Between Two Dates
Similarly, the formula to calculate the number of complete years ("y") is =DATEDIF(B1,B2,"y"). Although using the dates in the example above would result in 0 'complete' years, if you use a start date a year or more earlier you will see the result.

Formula To Calculate Years Between Two Dates_in_Microsoft_Excel_2007_2010_2013_2016_365

Calculate the Number of Years or Months Between Today and Some Other Date
Maybe you want to calculate the number of years or months between today and some other date.

For dates in the past (e.g. birth dates, hire dates, etc...), substitute the end_date with the TODAY() function. For dates in the future (e.g. retirement date, maturity date, etc...), substitute the start_date with the TODAY() function.

Formula To Calculate Years From Today_in_Microsoft_Excel_2007_2010_2013_2016_365

A few things to keep in mind about the DATEDIF function

1) The start_date must be less than or equal to the end_date, otherwise it will give an error.

2) Acceptable interval codes are "d", "m", "y", "ym", "yd", "md" (with quotes);

3) It may appear obvious what the "ym", "yd", and "md" interval codes mean but they require a second look. The "ym" interval code returns the number of months between the two dates as if they were in the same year and ignores the year. The "yd" and "md" interval codes returns the number of days between the two dates as if they were in the same year and ignores the year.

Datedif Function Options_in_Microsoft_Excel_2007_2010_2013_2016_365
Calculate the Number of Years, Months and Days Between Two Dates

To calculate the number of years, months and days between two dates you will need to create a formula using multiple DATEDIF functions. This formula will return a text string.

Again, assuming your start date is in cell B1 and your end date is in cell B2 you can use the following formula to return a text string showing the
number of years, months and days between those two dates.

=DATEDIF(B1,B2,"y") & " years, " & DATEDIF(B1,B2,"ym") & " months," & DATEDIF(B1,B2,"md") & " days"
To use this formula in your worksheet, Copy and Paste it from here into your Excel formula bar, then adjust the cell references.

Formula To Calculate Years Months Days Between Two Dates_in_Microsoft_Excel_2007_2010_2013_2016_365
As you probably have concluded, to calculate date differences based on today, you can substitute the TODAY() function for one of the date references in this formula.



Thanks for supporting this newsletter and website

FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week



Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that for a few dollars. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are ever not completely satisfied with anything I recommend, please let me know and I will get your money back for you...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630