Calculate the number of days between two dates in Excel
Calculating the number of days between two dates is one of the basic arithmetic operations and widely used today. This operation is used to calculate the number of working days/months/years, seniority, and number of days off, etc. This article will guide you on how to calculate the number of days between two dates in Excel. Please follow the steps below.
1. DATEDIF Function Syntax
To easily and quickly calculate seniority, we introduce to you the DATEDIF function.
Syntax:=DATEDIF(start_date, end_date, unit)
Where:
– Unit options include:
+ Unit = “d” => The function returns the number of days between two dates.
+ Unit = “m” => The function returns the number of months between two dates.
+ Unit = “y” => The function returns the number of years between two dates.
+ Unit = “yd” => The function returns the number of remaining days in the year between two dates.
+ Unit = “ym” => The function returns the number of remaining months in the year between two dates.
+ Unit = “md” => The function returns the number of remaining days in the month between two dates.
Before you enter the function into the data table in the above syntax, you need to perform the following steps:
Step 1:
Select the Options item on the File tab on the toolbar.
Calculate the number of days between two dates in Excel
Step 2:
On the screen, the Excel Options dialog box will appear. The user selects the Add-ins item and then moves to the Manage item and selects Go.
Calculate the number of days between two dates in Excel
Step 3:
The Add-Ins dialog box appears on the screen. Next, the user selects the first two items, Analysis ToolPak and Analysis ToolPak – VBA. After selecting, press OK.
Calculate the number of days between two dates in Excel
Then, the DATEDIF function can help you calculate seniority between any two dates.
2. Illustrative example
To better understand how to perform the calculation, please observe the example in the data table below.
1. Calculate the number of days worked
To calculate seniority based on the number of days worked, you use the optional parameter “d.”
You enter the formula: =DATEDIF(C2,D2,”d”). After clicking OK, you just need to drag the formula down to copy it for the remaining values. Then you will get the result as shown below:
Calculate the number of days between two dates in Excel
2. Calculate the number of months worked
To calculate the number of months worked, you also perform the same steps as above, but the optional parameter is “m.”
The user enters the formula: =DATEDIF(C2,D2,”m”). Then you can copy the formula for the values below to get the result as follows:
Calculate the number of days between two dates in Excel
3. Calculate the number of years worked
To calculate the number of years worked, you also perform the same steps as above, but the optional parameter is “y.”
You enter the formula: =DATEDIF(C2,D2,”y”). Then you just need to copy the formula for the values below. The result will be as shown below:
Calculate the number of days between two dates in Excel
4. Calculate seniority based on the DATEDIF function
Calculating work seniority means calculating the number of days, months, and years worked between two time frames. However, you need to use the “&” character to concatenate the strings together.
The user enters the formula: =DATEDIF(C2,D2,”y”)&” years “&DATEDIF(C2,D2,”m”)&” months “&DATEDIF(C2,D2,”d”)&” days”. After entering the formula, click OK to get the result. Then you can drag the mouse down to complete the result for the data table.
Calculate the number of days between two dates in Excel
The above article has guided you on how to calculate work seniority between two time frames in Excel. Hopefully, this article will provide useful information for readers. Good luck with your implementation!
