Count days, months, years with DATEDIF function in Excel

Content
  1. Calculate the difference between two dates in Excel
  2. Syntax and arguments of the DATEDIF function
  3. Calculate difference in days with DATEDIF
  4. DATEDIF Error Values

Calculate the difference between two dates in Excel

Function DATEDIF calculates the period or difference between two dates in days, months, and years. You can use the function: DATEDIF to determine a timetable for an upcoming project, or it can be used in conjunction with a person’s date of birth to calculate, for example, the person’s age in years, months, and days.

These instructions apply to Excel 2019, 2016, 2013, 2010, and Excel for Office 365.

Syntax and arguments of the DATEDIF . function

Function syntax refers to the layout of the function and includes the function name, parentheses, and arguments. Syntax for a function DATEDIF :

 = РАЗНДАТ (дата_начал, дата_окончание, "единица") 
  • starting date (necessary). You can specify the start date of the selected period or a cell reference to find this data on the worksheet.
  • end date (necessary). As with the start date, you can enter the end date of the selected period or a cell reference.
  • unit (required): The unit tells the function to find the number of days (“D”), full months (“M”), or full years (“Y”) between two dates. You must enclose the unit argument in quotes, such as “D” or “M”.

In addition to D, M, and Y, you can see three more unit options in the image below:

  • “YD” calculates the number of days between two dates, but ignores years ( line 5 ).
  • “JM” calculates the number of months between two dates, but ignores the day and year ( line 6 ).
  • “MD” calculates the number of days between two dates, but ignores the month and year ( line 7 ).

DATEDIF is a hidden function, so you won’t find it with other date functions on the Formula tab in Excel, which means you can’t use the Function dialog box to enter it. As a result, you have to manually enter the function and its arguments into the cell.

Excel performs date calculations by converting dates to serial numbers. December 31, 1899 – Serial Number 1 and January 1, 2008 – Serial Number 39488, as 39,488 days have passed since January 1, 1900.

Calculate difference in days with DATEDIF

The following shows how to enter the function: DATEDIF located in cell B2 as shown in the example above, to display the number of days between dates. May 4, 2014 and August 10, 2016 .

 = РАЗНДАТ (А2, А3, "D") 

Below are the steps to introduce this feature using cell references.

  1. Click cell B2 to make it the active cell; Displays the number of days between two dates.

  2. V cell B2 enter = datedif(

  3. Click cell A2 to enter a reference to that cell as an argument starting date for the function.

  4. Enter comma v cell B2 after cell reference A2 to act as a separator between the first and second arguments. ,

  5. Click cell A3 to enter a reference to that cell as an argument end date .

  6. Enter comma after cell reference A3 .

  7. as argument unit enter a letter d within quotation marks ( “D” ) to specify functions to display the number of days between two dates.

  8. Enter closure round bracket.

  9. Press key Enter on the keyboard to complete the formula.

  10. Amount of days – 829 – displayed in cell B2 sheet.

  11. By clicking cell B2 full formula appears in the formula bar above the worksheet.

DATEDIF Error Values

If you do not enter the data for the arguments in this function correctly, the following error values ​​will appear:

  • #WHERE THE!: . You will get this error if the start date or end date is are not a real date ( line 8 in the image below where cell A8 contains text data).
  • #NUM!. you will see if end date earlier than start date ( line 9 . below).

Leave a Reply

Your email address will not be published.