Excel Fast batch calculates the job seniority wages and does not exceed the number of years limit

Today, we will teach you how to use Excel to calculate the job seniority wages in a fast batch and not exceed the number of years online. New Year's Day is coming, most of the employees of the company's job seniority wages should rise again. Here for the benefit of everyone, can let us remember less labor. For example, we assume that job seniority wages is 50 yuan a year, 20 is the upper limit, more than 20 years in accordance with the 20 calculation, then we set up batch to calculate the different time of the staff job seniority wages.

Formula: MIN (20,DATEDIF (B2,today (), "Y")) *50

DATEDIF (start date, end date, "Y"/"M"/"D"): Calculates the number of years between the start and end dates, or the number of days of the month.

First use the DATEDIF function to calculate the seniority, if the seniority is less than 20, then return this value less than 20, if greater than the 20,min function will return 20, and then multiplied by 50.

Case demonstration

① the name of the salesperson and the date of entry, which stipulates that the job seniority wages is 50 yuan per year, capped 20, first of all, we enter in the C2 cell: =min (20,datedif (B2,today (), "Y") *50.

② carriage return, get results 650, because Sun Ning is 2000, now 2013, 13 years, 13*50=650, the answer is correct.

③ Double-click the cell fill handle to complete the calculation of the following cells.