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 chockablock 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 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 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 1Jan2016 and cell B2 contains 03Mar2016, you simply enter the formula =B2B1 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. 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(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. 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.


"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: 7098344630 