Excel function learning: due date calculation, several application examples of EDATE() function

Usually we always need to calculate various expiration times, such as contract expiration date, employee regularization date, product validity period, etc. The common practice is to use the DATE function to calculate, but the calculation formula of the DATE function is too cumbersome. Is there a simpler formula? Of course there is! Just use EDATE~

In daily work, we often encounter calculations such as the employee's regularization date, contract expiration date, employee retirement date, product validity expiration date, etc. Issues related to dates. These problems may seem complicated, but in fact they only require a very simple function, which is EDATE. Today I will share several application examples of the EDATE function. I hope it will be helpful to everyone.

Example 1: Calculate the employee's regularization date

The picture below is a registration form for new employees of a company. They can become regular employees after three months of employment. It must be calculated based on the entry date. Date of formalization.

No comparison means no harm. Let’s first take a look at how friends who don’t know the EDATE function write the formula:

=DATE(YEAR (D3),MONTH(D3) 3,DAY(D3))

The formula for using the EDATE function is like this: =EDATE(D3,3 ),Is not it simple?

The EDATE function requires only two parameters, the start date and the number of months between.

Formula=EDATE(D3,3) means the date corresponding to the date in cell D3 three months later.

Example 2: Calculate the contract expiration date

As shown in the figure, the corresponding contract expiration date needs to be calculated based on the date and contract period when each person signed the contract. Today, it is also very easy to calculate the result using the EDATE function.

Formula: =EDATE(D3,E3*12)-1

Different from the first example, this In the example, the date to be calculated is not the date 3 months later, but the date several years later. Because EDATE can only be calculated by month, you need to multiply the corresponding year in column E by 12 to turn it into the number of months to get the correct result.

Example 3: Calculate employee retirement date

As shown in the figure, the retirement date is calculated based on gender and date of birth. Males are over 60 years old and women are over 55 years old. retire.

Formula: =EDATE(C3,IF(B3="Male",60,55)*12)

##at In this example, retirement age is determined based on gender. If you are a male, the retirement age is 60, and the retirement date is 60 years after your birth date; similarly, if you are a female, the retirement age is 55, and your retirement date is 55 years after your birth date.

First, use the IF function to determine the retirement age of each person. This part of IF(B3="Male",60,55) is easy to understand. If the value of cell B3 is equal to male, 60 will be returned, otherwise 55 will be returned. .

Then use the method of Example 2 to multiply the number of years obtained by the IF function by 12 to convert it into a number of months, and then use the EDATE function to obtain the retirement date.

Example 4: Calculate the expiration date of the product validity period

As shown in the figure, the product validity period is calculated based on the production date and shelf life of each product.

Formula:

=EDATE(B2,IF(RIGHT(C2,1)="Year",LEFT(C2,1)*12,SUBSTITUTE(C2,"Month","") *1))-1

The difficulty of this example is that the shelf life is not a standardized number, and it has two forms: years and months. Let’s briefly analyze the idea of ​​​​this part of IF.

RIGHT(C2,1)="Year" The function of this part is to use the RIGHT function to extract the rightmost word of the cell in column C to determine whether the shelf life is calculated in years or months. If it is years, use LEFT to extract the number of years and multiply it by 12 to convert it to the number of months, which is LEFT(C2,1)*12.

(Note: This idea is based on the fact that under normal circumstances the shelf life will not exceed 10 years, so the leftmost digit is extracted as the year. If the year has two digits, you need to change to other ideas. .)

If it is not calculated by year, you need to extract the number of months. There are many ideas for extracting the number of months. This example uses the substitution function SUBSTITUTE to replace the word "month" in the cell with nothing (equivalent to deletion). The value obtained with the SUBSTITUTE function is text, so the text number needs to be converted into a numerical value by *1 at the end.

Finally, use the EDATE function to calculate the shelf life of the product.

Through the above four examples, everyone should have a clearer understanding of the usage of this function. The focus is on how to get the second parameter (number of months) based on actual problems. Sometimes you can use the data in the table directly, and sometimes You need to use other functions to get the required number of months.

The last point to note is that the number of months can be a negative number, which means that the date is found several months before the specified date.

Related learning recommendations: excel tutorial

The above is the detailed content of Excel function learning: due date calculation, several application examples of EDATE() function. For more information, please follow other related articles on the PHP Chinese website!