DATEDIF(), which suggests Date + Dif, is a compatibility operate left over from Lotus 1-2-3 that Microsoft adopted in Excel model 2000, which is the one model that explains how this operate works. It’s operational in all Excel variations, nevertheless it’s not on the Formulas menu or within the Help menus after Excel 2000. If your spreadsheet experiences started with Lotus, which is true for a lot of 1000’s of customers, you’ll be completely happy to know that this previous Lotus operate continues to be alive and kicking.
The goal of this operate is to calculate the time between a user-specified beginning and ending date in days, months, or years. The arguments for this operate are:
Start_date: begin date in Excel date serial quantity format
End_date: finish date in Excel date serial quantity format
Unit: the time unit to make use of (years, months, or days)
And the syntax appears to be like like this: =DATEDIF(start_date,end_date,unit)
The “unit” is specified utilizing the unit argument, which is a textual content code. For instance, the next codes clarify how these values are used within the operate’s syntax:
Use the letter “Y” to specify the distinction in full years
Use the letter “M” to specify the distinction in full months
Use the letter “D” to specify the distinction in days
Use the letters “MD” to indicate the distinction in days, and ignore months and years
Use the letters “YM” to indicate the distinction in months, and ignore days and years
Use the letters “YD” to indicate the distinction in days, and ignore years
You can use the DATEDIF() operate to find out somebody’s age in years, months, and days; to calculate your company anniversary date; to learn how previous your home equipment are or the age of your pc tools; to find out the variety of days, months, or years between two dates; to countdown the variety of days earlier than Christmas; and a lot extra.
Date codecs
You can enter dates for this formulation 4 other ways:
1. As serial numbers, which is Microsoft Excel’s distinctive methodology of storing dates so you should use them in calculations. When you enter a date, then use the General format, it shows as an Excel serial quantity.
For instance, to search out the variety of months between June 16, 2016 (start_date) and Oct 31, 2016 (end_date), write this formulation: =DATEDIF(42537,42674,”m”). The consequence for this formulation is 4 months.
JD SartainNote: Excel can not calculate dates earlier than January 1, 1900 on Windows PCs, and January 1, 1904 on Apple Mac techniques.
2. As cell references: that’s, you’ll be able to level to or enter the cell deal with. For instance, the variety of days between 10/10/2010 (in cell A5) and 11/11/2011 (in cell B5). The formulation for this instance is =DATEDIF(A5,B5,”d”). The results of this formulation is 397 days.
JD Sartain3. As textual content strings: that’s, dates within citation marks. You can enter the date in any format you want. Note, nevertheless, that should you enter the month and day with no yr, Excel assumes the present yr. The formulation for this instance is =DATEDIF(“12/12/2012”, “12/25/2015”, “y”). The results of this formulation is three full years.
JD Sartain4. Or, as a response to different capabilities such because the TODAY() operate or the NOW() operate. For instance, what number of days between NOW() and Christmas? Use this formulation to search out out: =DATEDIF(NOW(),”12/25/2016”,”d”). The reply is 70 days.
Note : Remember, when utilizing the NOW() or TODAY() operate, the reply adjustments daily. So, should you open this spreadsheet tomorrow, the reply might be sooner or later fewer.
JD SartainRounding outcomes up or down
1. The DATEDIF() operate all the time rounds down (by default) to the closest entire month or yr.
2. If you wish to calculate months or years rounded up, add half a month or half a yr to the formulation like this: =DATEDIF(A19,B19+15,”m”) for half a month (or 15 days) or =DATEDIF(A21,B21+183,”y”) for half a yr (or 183 days). The result’s now rounded as much as the closest month or yr.
JD SartainNesting DATEDIF() capabilities
1. You may also nest the DATEDIF() operate, mix it with different Excel capabilities akin to TODAY() and NOW(), akin to above, or nest it inside itself to get all three arguments—the variety of years, months, and days. For instance, to get years, months, and days between March 15, 2011 and December 7, 2016, enter this formulation utilizing cell references as a substitute of precise dates: =DATEDIF(A23,B23,”y”) &” years, “&DATEDIF(A23,B23,”ym”) &” months, “ &DATEDIF(A23,B23,”md”) &” days”. The reply is 5 years, 8 months, 22 days.
JD SartainNote: Using cell references is all the time higher than hard-coding the date into the formulation like this: =DATEDIF(“3/15/11”, “12/7/16”, “d”), as a result of you’ll be able to simply change the date on the spreadsheet by simply getting into or copying a brand new date on high of the previous one. If the date is hard-coded into the formulation, you need to open up and edit every formulation separately. And once you copy a hard-coded formulation, you find yourself with the identical date in every of the copied cells.
2. If you wish to discover out precisely how previous Whoopi Goldberg is right now, enter her birthday (11/13/1955) in cell A24, then enter this formulation in cell E24: =DATEDIF(A24,TODAY(),”y”)&” years, “&DATEDIF(A24,TODAY(),”ym”) &” months, “&DATEDIF(A24,TODAY(),”md”)&” days”. The reply (on the time I made this spreadsheet a couple of months in the past) is 60 years, 11 months, and 3 days, and naturally if I opened this spreadsheet right now, it could be an extended time.
JD SartainFor extra data concerning DATEDIF() capabilities, begin with this Microsoft Office Support page, or search on-line, or discover a packrat pal who nonetheless has the Microsoft Excel 2000 reference handbook.