Meet DATEDIF(), Excel’s secret Date & Time function that’s still handy - vallieresurriess

DATEDIF(), which means Date + Dif, is a compatibility function left over from Lotus 1-2-3 that Microsoft adopted in Stand out version 2000, which is the merely version that explains how this function works. It's operational in altogether Excel versions, but it's not on the Formulas menu or in the Assistance menus after Stand out 2000. If your spreadsheet experiences began with Lotus, which is true for numerous thousands of users, you'll be happy to know that this old Lotus function is still liveborn and kicking.

The purpose of this function is to calculate the time between a user-nominal starting and ending date in years, months, or years. The arguments for this function are:

Start_date: come out date in Excel date serial number format

End_date: end date in Excel date serial number arrange

Whole: the time unit to use (years, months, or years)

And the syntax looks like this: =DATEDIF(start_date,end_date,unit)

The "unit" is mere using the unit disputation, which is a textual matter code. For example, the following codes excuse how these values are used in the function's syntax:

Use the letter "Y" to specify the divergence in full years

Utilisation the letter "M" to specify the difference fully months

Use the letter "D" to specify the difference in days

Function the letters "MD" to show the deviation in days, and ignore months and geezerhood

Use the letters "YM" to show the difference in months, and ignore years 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 joint day of remembrance date; to find how old your appliances are or the age of your data processor equipment; to settle the number of days, months, or years between two dates; to countdown the number of days before Christmas Day; and then much more.

Date formats

You can embark dates for this rul four different ways:

1. American Samoa serial Numbers, which is Microsoft Excel's unique method of storing dates so you can habit them in calculations. When you enter a go steady, past use the Indiscriminate format, IT displays as an Excel serial number.

JD Sartain

For example, to witness 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 quartet months.

JD Sartain

Note: Surpass cannot calculate dates before Jan 1, 1900 connected Windows PCs, and Jan 1, 1904 on Apple Macintosh systems.

2. Eastern Samoa cell references: that is, you prat point to or enter the cell call. For example, the number of years 'tween 10/10/2010 (in cell A5) and 11/11/2011 (in cell B5). The pattern for this object lesson is =DATEDIF(A5,B5,"d"). The result of this formula is 397 days.

JD Sartain

3. As text strings: that is, dates inside of quotation marks. You can enter the go steady in any format you like. Preeminence, however, that if you go in the month and daylight with no year, Excel assumes the stream year. The formula for this example is =DATEDIF("12/12/2012", "12/25/2015", "y"). The consequence of this formula is three full years.

JD Sartain

4. Or, as a response to other functions such as the TODAY() function or the NOW() function. For case, how many years 'tween NOW() and Christmas? Use this formula to learn: =DATEDIF(NOW(),"12/25/2016","d"). The suffice is 70 years.

Note : Retrieve, when victimization the NOW() or Nowadays() function, the answer changes every day. So, if you open this spreadsheet tomorrow, the answer will be one Clarence Shepard Day Jr. fewer.

JD Sartain

Rounding results up operating room down

1. The DATEDIF() function always rounds belt down (by default) to the nearest unanimous month or year.

2. If you neediness to calculate months or years oval-shaped 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) Beaver State =DATEDIF(A21,B21+183,"y") for fractional a yr (or 183 days). The resultant role is directly hyperboloidal up to the nighest month or yr.

JD Sartain

Nesting DATEDIF() functions

1. You can also nest the DATEDIF() function, combine it with other Excel functions such as Nowadays() and NOW(), such as above, or nestle it within itself to get all three arguments—the number of years, months, and days. For example, to get years, months, and years between Edge 15, 2011 and December 7, 2016, enter this formula using cell references instead of actual dates: =DATEDIF(A23,B23,"y") &" years, "&A;DATEDIF(A23,B23,"ym") &" months, " &DATEDIF(A23,B23,"md") &" years". The answer is 5 years, 8 months, 22 days.

JD Sartain

Note: Using cell references is always better than unmerciful-secret writing the date stamp into the pattern 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 red-hot go out on top of the onetime one. If the date is nasty-coded into the formula, you have to open upwards and edit each rule one by one. And when you simulate a hard-coded formula, you end up with the same date in each of the copied cells.

2. If you want to find retired 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")&" days, "&DATEDIF(A24,Nowadays(),"ym") &ere;" months, "&A;DATEDIF(A24,TODAY(),"md")&adenosine monophosphate;" days". The response (at the fourth dimension I successful this spreadsheet a few months ago) is 60 old age, 11 months, and 3 days, and of flow from if I opened this spreadsheet nowadays, information technology would be a longer time.

JD Sartain

For additional information regarding DATEDIF() functions, set about with this Microsoft Office Support page, or search online, or find a bushytail woodrat friend who still has the Microsoft Excel 2000 reference manual.

Source: https://www.pcworld.com/article/410695/meet-datedif-excels-secret-date-time-function-thats-still-handy.html

Posted by: vallieresurriess.blogspot.com

Share this post