Its operational in all Excel versions, but its 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, youll be happy to know that this old Lotus function is still alive and kicking.
![]() The arguments for this function are: Startdate: start date in Excel date serial number format Enddate: 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(startdate,enddate,unit) The unit is specified using the unit argument, which is a text code. Datedif Excel Full Years UseFor example, the following codes explain how these values are used in the functions 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 someones 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. ![]() When you enter a date, then use the General format, it displays as an Excel serial number. For example, the number of days between 10102010 (in cell A5) and 11112011 (in cell B5). 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(12122012, 12252015, y). For example, how many days between NOW() and Christmas Use this formula to find out: DATEDIF(NOW(),12252016,d). 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. The DATEDIF() function always rounds down (by default) to the nearest whole month or year. 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,B1915,m) for half a month (or 15 days) or DATEDIF(A21,B21183,y) for half a year (or 183 days). 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 argumentsthe 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. If the date is hard-coded into the formula, you have to open up and edit each formula one at a time. 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. She writes the Max Productivity column for PCWorld, a monthly column for CIO, and regular feature articles for Network World.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2020
Categories |