DATEDIF(), which means Date + Dif, is a compatibility function left over from Lotus 1-2-3 that Microsoft adopted in Excel version 2000, which is the only version that explains how this function works. It’s operational in all Excel versions, but it’s not on the Formulas menu or in the Help menus after Excel 2000. If your spreadsheet experiences began with Lotus, which is true for many thousands of users, you’ll be happy to know that this old Lotus function is still alive and kicking.
The purpose of this function is to calculate the time between a user-specified starting and ending date in days, months, or years. The arguments for this function are:
Start_date: start date in Excel date serial number format
End_date: end date in Excel date serial number format
Unit: the time unit to use (years, months, or days)
And the syntax looks like this: =DATEDIF(start_date,end_date,unit)
The “unit” is specified using the unit argument, which is a text code. For example, the following codes explain how these values are used in the function’s syntax:
Use the letter “Y” to specify the difference in full years
Use the letter “M” to specify the difference in full months
Use the letter “D” to specify the difference in days
Use the letters “MD” to show the difference in days, and ignore months and years
Use the letters “YM” to show the difference in months, and ignore days and years
Use the letters “YD” to show the difference in days, and ignore years
You can use the DATEDIF() function to determine someone’s age in years, months, and days; to calculate your corporate anniversary date; to find out how old your appliances are or the age of your computer equipment; to determine the number of days, months, or years between two dates; to countdown the number of days before Christmas; and so much more.
Date formats
You can enter dates for this formula four different ways:
1. As serial numbers, which is Microsoft Excel’s unique method of storing dates so you can use them in calculations. When you enter a date, then use the General format, it displays as an Excel serial number.
For example, to find the number of months between June 16, 2016 (start_date) and Oct 31, 2016 (end_date), write this formula: =DATEDIF(42537,42674,”m”). The result for this formula is four months.
Note: Excel cannot calculate dates before January 1, 1900 on Windows PCs, and January 1, 1904 on Apple Mac systems.
2. As cell references: that is, you can point to or enter the cell address. For example, the number of days between 10/10/2010 (in cell A5) and 11/11/2011 (in cell B5). The formula for this example is =DATEDIF(A5,B5,”d”). The result of this formula is 397 days.
3. As text strings: that is, dates inside of quotation marks. You can enter the date in any format you like. Note, however, that if you enter the month and day with no year, Excel assumes the current year. The formula for this example is =DATEDIF(“12/12/2012”, “12/25/2015”, “y”). The result of this formula is three full years.
4. Or, as a response to other functions such as the TODAY() function or the NOW() function. For example, how many days between NOW() and Christmas? Use this formula to find out: =DATEDIF(NOW(),”12/25/2016”,”d”). The answer is 70 days.
Note : Remember, when using the NOW() or TODAY() function, the answer changes every day. So, if you open this spreadsheet tomorrow, the answer will be one day fewer.
Rounding results up or down
1. The DATEDIF() function always rounds down (by default) to the nearest whole month or year.
2. If you want to calculate months or years rounded up, add half a month or half a year to the formula like this: =DATEDIF(A19,B19+15,”m”) for half a month (or 15 days) or =DATEDIF(A21,B21+183,”y”) for half a year (or 183 days). The result is now rounded up to the nearest month or year.
Nesting DATEDIF() functions
1. You can also nest the DATEDIF() function, combine it with other Excel functions such as TODAY() and NOW(), such as above, or nest it within itself to get all three arguments—the number of years, months, and days. For example, to get years, months, and days between March 15, 2011 and December 7, 2016, enter this formula using cell references instead of actual dates: =DATEDIF(A23,B23,”y”) &” years, “&DATEDIF(A23,B23,”ym”) &” months, “ &DATEDIF(A23,B23,”md”) &” days”. The answer is 5 years, 8 months, 22 days.
Note: Using cell references is always better than hard-coding the date into the formula like this: =DATEDIF(“3/15/11”, “12/7/16”, “d”), because you can easily change the date on the spreadsheet by just entering or copying a new date on top of the old one. If the date is hard-coded into the formula, you have to open up and edit each formula one at a time. And when you copy a hard-coded formula, you end up with the same date in each of the copied cells.
2. If you want to find out exactly how old Whoopi Goldberg is today, enter her birthday (11/13/1955) in cell A24, then enter this formula in cell E24: =DATEDIF(A24,TODAY(),”y”)&” years, “&DATEDIF(A24,TODAY(),”ym”) &” months, “&DATEDIF(A24,TODAY(),”md”)&” days”. The answer (at the time I made this spreadsheet a few months ago) is 60 years, 11 months, and 3 days, and of course if I opened this spreadsheet today, it would be a longer time.
For additional information regarding DATEDIF() functions, start with this Microsoft Office Support page, or search online, or find a packrat friend who still has the Microsoft Excel 2000 reference manual.
0 comments:
Post a Comment