Hi fellow Excel Addict,
Thanks for joining me for another 'Excel in Minutes' tip. I received a lot of great feedback on my Sparklines tip last week.
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)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Excel's 'Secret' Function for Calculating Date Differences
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
|"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