To calculate the number of days difference between 2 dates in Excel that support functions, you can in a snap to calculate the number of days between any two time points.
In Excel, to calculate any number of dates between two dates you should use the DATEDIF function .
Syntax: DATEDIF (start date, end date, optional)
Inside:
- Options include the following options:
+ Option = d => The function will return the number of days between the two time points.
+ Option = m => The function will return the number of months between the two time points.
+ Option = y => The function will return the number of years (only take the whole part) between 2 time points.
+ Option = yd => The function will return the odd number of days of the year (ie the number of days yet to round the year) between two time points.
+ Option = ym => The function will return the number of odd months of the year (the number of months yet to round the year) between two time points.
+ Option = md => The function will return the odd number of days of the month (number of days not yet full of the month) between two time points.
Meaning: The function performs the number of years, months, days, the odd number of months in the year, the odd number of days in a month.
Note: The DATEDIF function is not included in the Excel library of functions, so before using it, turn on the Analysis ToolPak feature .
Step 1: On the card File -> Options .
Step 2: A dialog box appears, click Add-ins, scroll down to the Manage section, click Go .
Step 3: A dialog box appears and select the Analysis ToolPak and Analysis ToolPak -VBA -> click OK .
So you can use the DATEDIF function to calculate the number of days difference between 2 dates in Excel.
For example:
For example, the data sheet calculates the number of days, months, years, and seniority of employees who have retired.
1. Calculate the number of working days
Enter the formula: = DATEDIF (D8, E8, "d") .
Copying the formula for the remaining values results in:
2. Calculate the number of months worked
You only need to replace the optional parameter as m:
Enter the formula: = DATEDIF (D8, E8, " m" ) -> copy the formula for other values to get results:
3. Calculate the number of days worked
Replace the optional parameter y:
Enter the formula: = DATEDIF (D8, E8, " y" ) -> copy the formula for other values to get results.
4. Calculating seniority based on Datedif function
- Calculating seniority is the number of days, months and years of work, here use more characters & to concatenate:
- Enter the formula: = DATEDIF (D8, E8, "y") & "year" & DATEDIF (D8, E8, "m") & "month" & DATEDIF (D8, E8, "d") & "date" .
Pressing Enter results:
The above article helps you to calculate the number of days difference between 2 dates in Excel hoping to help you. Good luck!