If Date 1 or Date 2 is not a valid date you will get a #VALUE error. If Date 1 is later than Date 2 Excel will return a #NUM error. Here's how it does this: First the DATE function creates the date, 5/1/ It creates it using the year in cell E17, and the month in cell E Then the 1 represents the first day of that month. This formula subtracts the first day of the ending month (5/1/) from the original end date in cell E17 (5/6/). Results 1 to 8 of 8 The 'DATEDIF' Function is not listed on my Excel program. Now follow the instructions at the top of that screen. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again.The DatedIf function was created many years ago in order to provide support for older workbooks from Lotus So even though. OP needs to write the complete syntax of DATEDIF. Hence, it doesn't pop up the syntax of DATEDIF if DATEDIF is written. It doesn't appear in Insert Function list though. It has been officially recognised and introduced by Microsoft in Excel and is present in Help also.If you are not sure A2 is small date where else A1 cell has bigger date, you can prevent Microsoft Excel to display error using ERRORIF function for error handling. So we can get compute age of a person in Excel using the below formula: Md interval parameter returns the number of days (d) remaining after all months (m) are excluded. Yd interval parameter returns the number of days (d) remaining after all years (y) are excluded. Ym interval parameter returns the number of months (m) remaining after all years (y) are excluded. These new intervals work like a mod function. These additional DateDif intervals are ym, yd and md intervals. Write todays date at A1 cell and the birthdate of the person at A2 cell.īesides the time intervals 'y', 'm' and 'd' we have additional time intervals that can be used with Excel DATEDIFF function. Let's assume that you want to calculate the age of a person who was born at 2nd of June in 1995. Let's apply this formula on an Excel age calculation formula to build age function. This complex date difference formula will use a number of Excel DateDif function to form the desired output. If your requirement is to express date difference in forms of a combination of different time intervals like 25 years 3 months and 12 days, then you need a more complex formula.

=Ě1-A2 - Substructing smaller date from bigger date in forms of dayĬalculate Age Function in Excel Worksheet =ĝATEDIF( A2 A1 'd' ) - Returns time as intervals of day =ĝATEDIF( A2 A1 'm' ) - Returns time as intervals of month =ĝATEDIF( A2 A1 'y' ) - Returns time as intervals of year

Datedif not in excel 2016