Excel hidden function DATEDIF

This article brings you relevant knowledge about excel, which mainly introduces the related issues of hidden functions, mainly the DATEDIF function. Let’s take a look at it together. I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

There is a type of function in Excel called hidden functions. What is the function list in Excel? They can't be found, and there are no relevant instructions even in Microsoft's help files, but they are not only powerful, but also widely used in work.

DATEDIF is a magical hidden function that exists in Excel, but it cannot be found in the Excel help file.

Today we will take a look at this magical hidden function.

1. Detailed explanation of DATEDIF function

The basic syntax of DATEDIF is:

DATEDIF (start date, end date, interval type)

Among them, the parameters start_date and end_date are two dates, and the former must not be greater than the latter.

unit has the following 6 parameters, which are used to calculate different differences, as shown in the table below.

Parameters of DATEDIF

In daily spelling, some people will miss the D in the middle of the function name and change it to DATEIF. This is wrong, and when the input is wrong, the Excel system will not will prompt.

This function word has a simple way to remember: DATEDIF is abbreviated from Date Different, which is translated as different dates.

2. Calculate the year, month, and day intervals between two dates

As shown in the figure below, this is the conventional usage of DATEDIF. The actual meaning of these six parameters can be combined with the figure Explain the data in .

General usage of DATEDIF

First, enter the following formulas in cells D16 and D24 respectively, and copy them down to cells D21 and D29 respectively. Calculate the difference between different parameters:

=DATEDIF(E16,F16,C16)

=DATEDIF(E24,F24,C24)

D24 cell, parameter "Y "Looking at 2017 and 2020 alone, the difference in years should be 3, but from 2017/7/28 to 2020/2/8, 2 years passed first to 2019/7/28, and it has not yet reached the required 2020/2/ 8. If one year passes, it will be 2020/7/28, which will exceed the end date, so the result returns 2 and cannot return 3.

Cell D25, parameter "M", 30 months after 2017/7/28, it will reach 2020/1/28, and then 1 month later, it will reach 2020/2/28, which exceeds the end date 2020/2/8, so the result can only be 30, not 31. We must fully understand the meaning of the word "whole" in "the number of whole years" and "the number of whole months".

Cell D26, parameter "D" is equivalent to directly subtracting two dates to calculate the difference in days.

Cell D27, parameter "MD", this calculation ignores the month and year, which is equivalent to pulling start_date to the closest date before end_date. That is to say, bring 2017/7/28 closer to the closest date with a date of 28 before 2020/2/8, that is, 2020/1/28, and then calculate the distance between 2020/1/28 and 2020/2/8 The difference in days is 11 days.

Cell D28, parameter "YM", ignore the day and year to calculate the number of whole months, which is equivalent to bringing 2017/7/28 to the closest July 28 before 2020/2/8. becomes 2019/7/28, and then calculate the "whole" number of months difference between it and 2020/2/8, that is, 6 months.

Cell D29, parameter "YD", ignores the difference in days in year calculation, which is equivalent to pulling start_date to the closest date of the same month and day before end_date. That is to say, bring 2017/7/28 closer to 2019/7/28, and then calculate the number of days difference between 2019/7/28 and 2020/2/8, which is 195 days.

When using the "MD" and "YD" parameters to calculate the day difference, due to the existence of leap years, sometimes there will be a difference of one day from the ideal value. This situation generally does not affect our daily use.

3. The difference between the whole year, month and day

As shown in the figure below, 2017/7/28 to 2020/7/27 and 2017/7/28 to 2020/7 are listed /28 comparison, although the end_date is only 1 day different, the results are quite different. The calculation principle is the same, and you need to understand the meaning of the word "whole".

The difference between the whole year, month and day

How do you need to remember so many parameters? First, you must know the role of this function and understand the calculation principle of each parameter. If you often need to calculate dates at work, you can print it out and stick it next to your desk for immediate reference.

4. Case: Calculation of length of service

Assume today is 2019/7/28, and the date each employee started working is as shown in column C in the figure below, then what is the length of service of each person? ? The length of service can be expressed as m years and n months.

Seniority calculation

can be performed step by step. First calculate the number of "whole years" and then the number of "whole months". When calculating the number of months, you need to note that the maximum value of the number of months will not exceed 11, because 12 months will be one year, that is, the existence of the year must be ignored to calculate the number of months. So which parameter to use for calculation?

As can be seen from the parameter comparison table of DATEDIF mentioned in the previous section, the parameter "Y" is used to calculate the number of whole years, and the parameter "YM" is used to calculate the number of whole months regardless of the year. So the function formula in cell D51 can be written as:

(drag left and right to view the complete formula)

=DATEDIF(C51,”2019/7/28″,”Y”)&” year “&DATEDIF(C51,”2019/7/28″,”YM”)&” Month”

Let’s take a look at the cell range D54:D56. The calculation results are different with only a difference of 1 day. Therefore, when using DATEDIF, you must always have the concept of "whole" in mind.

In addition, the Y, M, and D parameters in DATEDIF are both uppercase and lowercase.

5. Case: Calculation of Annual Leave Days

"Regulations on Paid Annual Leave for Employees" stipulates that if an employee has worked for one year but less than 10 years in total, the annual leave is 5 days; If the employee has less than 20 years of experience, the annual leave is 10 days; if the employee has more than 20 years of experience, the annual leave is 15 days.

Similarly, assuming today is 2019/7/28, how many days of annual leave are each employee’s days?

In fact, this question is simpler than the case in the previous section. You only need to know the number of years each employee has worked.

As shown in the figure below, enter the following formula in cell D66 to calculate the number of working years of each employee:

=DATEDIF(C66,DATE(2019,7,28)," Y”)

Annual leave days calculation

I emphasize again that if you use the shortcut input method to express the date in the formula, you must add double quotes, as above "DATEDIF(C51,"2019/7/28″,"Y")" in the section, if you can't master the use of double quotes, just use the DATE function in a disciplined manner to ensure that there will be no errors.

Based on the number of years in column D, you can calculate the number of days of statutory annual leave. Enter the following formula in cell E66:

=LOOKUP(D66,{0,1,10,20},{ 0,5,10,15})

Related learning recommendations: excel tutorial

The above is the detailed content of Excel hidden function DATEDIF. For more information, please follow other related articles on the PHP Chinese website!