Free All-in-One Office Suite with PDF Editor
Edit Word, Excel, and PPT for FREE.
Read, edit, and convert PDFs with the powerful PDF toolkit.
Microsoft-like interface, easy to use.
Windows • MacOS • Linux • iOS • Android
Use the DATEDIF function to calculate the intervals of dates
Uploaded time: August 30, 2021 Difficulty Intermediate
Use the DATEDIF function to calculate the intervals of dates
Use the DATEDIF function to calculate the intervals of dates
The DATEDIF function is mainly used to calculatedays, months, or years between two dates.
Now we are going to use this table to show you the usage of this function. This table records the entry date of the company’semployees. Now we want to calculate the employee’s year of service. We can first click cell C2 and then click Insert Function. Insert DATEDIF in the Search for Function of the dialog box. Click the OKbutton, and then the Function Argumentsdialog box will pop up.
Start date refers to the date we want to start with, which corresponds to the employment date data in column B of the table. Click on cell B2 here.
End date refers to the date we want to stop the calculation, which is actually the deadline 9/1/2021 on the right side of the table.Click cell E7 here, then press F4 to add an absolute reference and lock the End date calculation location.
Pleasenotice that the end date here should be greater than the start date.
Comparison Unit refers to the return type of the required information. That is to say, different parameters represent different return results. Y is the number of years between these two dates, M is the number of months between these two dates, and D is the number of days between these two dates.Remember to add double quotes when entering parameters.Enter Y here.
Now click the OK button to get the result.Then, move the mouse cursor to the lower right corner of the cell. Pull it downto get the corresponding calculation data when the cursor turns into a black cross.
At this time, we can see that the resulting data are all integers, and those digits after the decimal point are all deleted. For example, the entry time of Louie is only nearly one month. Since it's less than one year, only the number 0 is displayed.
In addition, we can also ignore the difference in years between the two dates and only calculate the number of days between the dates. Let's take Louie's entry date as an example again. Click cell D10, and then click Insert Function to select the DATEDIF function.Click cell B10 for Start date, click E7 for End date, and enter YD for Comparison Unit.Now click the OK button, then we can get the number of days between August 9th and September 1st. The result is 23 days.
Also, the parameter MD in the Comparison Unit represents the number of days between two dates while ignoring the difference in years and months between the two dates.YM meanscalculating the number of months between two dates while ignoring thedifference in years between the two dates.
Easy your work with WPS Spreadsheet like Microsoft Excel. Did you get it?
Also Read:
- 1. How can we use RANK function
- 2. A quick way to calculate sum, average, count, max, and min
- 3. SUBTOTAL function: Calculate the data with the hidden value
- 4. Quick to learn the time and dates functions
- 5. Use the Today function to quickly calculate days between dates
- 6. Use SUM function to quickly calculate the sum
Does this video help you?