Sharing practical Excel skills: It turns out that calculating overtime pay is so easy!

In the previous article " Practical Excel skills sharing: three tricks to fill in alternate rows, the tricks are exciting and full of style! ", we learned about the method of filling in alternate rows. Today we are going to talk about Excel table statistics and share with you a tutorial. Excel beginners can use the method of adding auxiliary columns. Friends who are proficient in Excel can directly use the function formula method. It can be done in one second. It is not too simple. !

The specific rules for overtime pay are: the start and end times of overtime are based on the attendance record, and the overtime pay is 120 yuan per hour. Less than 30 minutes will not be calculated, and more than 30 minutes will not be calculated. One hour is calculated as 30 minutes (0.5 hours).

According to the above billing rules, we can add auxiliary columns to convert overtime hours and then calculate the cost. So how to convert and calculate overtime pay based on the start and end time in accordance with regulations is what we are going to learn today. First, let’s take a look at the format of the overtime statistics table:

Table Columns A to D are basic information, and the data can be obtained through the attendance machine. Columns E to I are calculated by formulas, which is what we focus on learning today. Let’s take a look at how each column is obtained.

1. Calculation of overtime hours

The formula is: =HOUR(D3-C3)

A time function HOUR is used here to extract the hour digit from the data obtained from column D minus column C. For example, D3-C3=2 hours and 15 minutes, you can use the HOUR function to get the result. 2.

2. Calculation of overtime minutes

The formula is: =MINUTE(D3-C3)

Similar to the HOUR function, MINUTE can extract the minute digit of the data obtained by subtracting column C from column D. For example: D3-C3=2 hours and 15 minutes. The MINUTE function can get the result 15.

Through these two formulas, we divide the overtime time into two parts according to hours and minutes. The next thing we need to do is to convert the minutes in 30-minute units according to the company's overtime regulations.

3. Minutes conversion

The calculation formula is: =FLOOR(F3,30)

A function FLOOR that rounds by a specified multiple is used here. The function requires two parameters, and the basic format is: FLOOR (the value to be rounded, the multiple to be rounded). Let’s take a simple example to see the effect of the function: =FLOOR(M2,3)

The formula changes a set of values ​​in column M according to 3 Rounding is performed by multiples of , and N columns are obtained. If it is less than 1 times 3, it will be rounded off and 0 will be returned; if it is 1 times but less than 2 times, it will be rounded to 3; if it is 2 times but less than 3 times, it will be rounded to 6, and so on.

Similarly, when we convert overtime minutes, we must use 30 minutes as the standard for rounding. The second parameter of the function is naturally 30. If it is less than 30, it will be zero. If it is greater than or equal to 30 and less than 60, it will return 30.

Through the above three steps, we have obtained the number of overtime hours and converted minutes. Next, we need to calculate the overtime hours used for billing.

4. Overtime billable hours

Calculation formula: =E3 G3/60

Here G3/60 means converting the converted minutes into hours.

5. Calculation of overtime pay

Formula: =H3*120

The calculation of overtime pay is very simple, just use overtime hours × 120.

Some friends may find the above method a bit troublesome. In order to calculate the overtime hours, three auxiliary columns are used. Can the overtime hours be calculated directly?

Of course, this requires the use of a very powerful function TEXT, the formula is: =FLOOR(TEXT(D3-C3,"[m]")/60,0.5)

The auxiliary column method is suitable for novices, and the idea is relatively simple. Although using the TEXT function simplifies the calculation steps, the formula is more difficult. Let me briefly explain the meaning of this formula.

TEXT(D3-C3,"[m]") This part displays the time difference in minute format, and the result is shown in column G. The TEXT function can convert a numerical value into text in a specified format. [m] This format code indicates that the elapsed time is displayed in minutes, and any less than one minute is rounded off. The most mysterious thing about the TEXT function is that it can use numerous format codes to obtain various display results.

After understanding this, the following will be easy to understand. Divide the minutes by 60 to convert them into hours, and then use the FLOOR function to round the hours by a multiple of 0.5. , you get the overtime hours.

Summary:

1. In today’s example, we used two time functions HOUR and MINUTE. These two functions are often used in attendance calculations. Yes, the function is also very easy to understand;

2. When calculating the time difference, you can directly subtract;

3. I believe that many friends may be exposed to the FLOOR function for the first time. When encountering rounding by a fixed multiple, it is very convenient to use this function;

4. Come into contact with the application of the TEXT function in time calculation. This is a very important knowledge point. If you are familiar with this function If you are interested, you can leave a message. We will publish some tutorials on the usage of this function in the future.

That’s it for today’s tutorial. Do you know how to calculate overtime pay? Whether it is adding an auxiliary column or directly using a function formula, it is actually very simple. As long as you practice more, you will soon master it, and you will no longer have to worry about calculating overtime pay!

Related learning recommendations: excel tutorial

The above is the detailed content of Sharing practical Excel skills: It turns out that calculating overtime pay is so easy!. For more information, please follow other related articles on the PHP Chinese website!