excel function Formula 5 for calculating overtime hours

Try to start from the middle and type ::in h6

Rounded in hours.

round(24*max(time(mid(h5,7,2),mid(h5,10,2),)time(17,30,))1)

In hours, rounded off decimals.

int(48*max(time(mid(h5,7,2),mid(h5,10,2),)time(17,30,))/2

In units of 1 hour (i.e. rounded off decimals).

int(24*max(time(mid(h5,7,2),mid(h5,10,2),)time(17,30,))

In 1-hour increments (rounded).

round(24*max(time(mid(h5,7,2),mid(h5,10,2),)time(17,30,))0)

Note that the format of h6 is set to a numeric value.

Your ** does not have line numbers and column labels, and it is difficult to write formulas.

Also, is your commute time in one cell, or in two cells?

Tianyudun found that the picture you gave was not derived from the attendance system, it was you who had been transformed, you thought we couldn't understand it, you sent the original picture to get it, you changed it messily.

Hello, Fang. 1. First of all, we open the daily recorded commuting time, as well as the actual off-duty time of excel**, 2, and then we enter the first function =text() below the overtime hours, 3, and then enter the mod() function in text.

4. In the mod() function, use the actual off-duty time minus the off-duty time, 5. In order to distinguish between hours and minutes, we add a description later, hh hours m minutes, 6. After entering to confirm, the overtime time will be calculated, 7. Other cells, just drag it out.

Article 36 of the new Labor Law stipulates that the daily working hours of workers shall not exceed eight hours and the average weekly working hours shall not exceed forty-four hours.

1. Calculation of working hours of the system.

Annual working days = 365 days - 104 days (rest days) - 11 days (statutory holidays) 250 days.

Quarterly working days = 250 days 4 quarterly days quarter.

Monthly working days = 250 days in December and days in months.

Number of hours worked = 8 hours of working days per month, quarter, or year.

2. Conversion of daily and hourly wages.

Daily wage = monthly salary income Number of days in the month.

Hourly wage = monthly salary income (8 hours of paid days per month).

Monthly Salary Days (365 days-104 days) December Note: The 11 statutory holidays stipulated by the state are not excluded when converting daily wages and hourly wages.

If the working hours are extended on normal working days, no less than 150% of the wages shall be paid;

If overtime is arranged on a rest day without compensatory leave, no less than 200% of the salary shall be paid;

If overtime work is arranged on statutory holidays, no less than 300% of the salary shall be paid.

F2 input: =hour(d2-c2)+if(minute(d2-c2) 60>=,,0)-8-e2

Drop-down fill formula. Hit.

The first disadvantage of the F column Zhiqinhu format is set as a numeric value, and the number of decimal places: 1 digit) <>

10 hours of weekday work during the day shift bn4 =SUMProduct((C$3:BL$3=.)"d")*C4:BL410))*10 Overtime hours for 10 hours outside the day shift =SumProduct((C$3:.)

bl$3="d"Shouting Envy)*(C4:BL4>10)*C4:BL4)-SumProduct((C$3:

bl$3="d")*C4:BL4>10))*10 If the formula for the night shift is not much worse than Zheng Tan, the formula above will be used"d"Replace all with .

Using a function formula is a bit cumbersome. It's better to insert a new table, find the overtime value for the start and the original quietly staring at the table (use = original table from Monday to Friday), and add a column to find the sum of the branches.

The formula you want:

sumproduct(if(b2:ae2>17/24,b2:ae2-17/24,b2:ae2+7/24)*(b2:ae2<>"

array formula, and press Ctrl+Shift+Enter at the same time to end the input.

The resulting result is in days, use the result *24 if you need to convert to hours, use the result *1440 if you need to convert to minutes, and format the cells yourself if you need to display the time format.

=sumproduct((b2:ae2>=time(17,,)b2:ae2-time(17,,)24+sumproduct((b2:

ae2"")mod(b2:ae2-time(17,,)1))*24

Or. =(sumproduct((b2:ae2"")mod(b2:

ae2-time(17,,)1))+sumproduct((b2:ae2>time(17,,)b2:ae2-time(17,,)24

The results are set to a regular format and are in hours.

1. First of all, record everyone's name and corresponding clock-in time in Excel.

2. Then click the "Data" tab in the menu bar, and find the "Column" in the "Data Tools" we need in the Data tab.

Add a conditional judgment function, if the time is less than 8:00, add 24:00, and it's as simple as that.

1. First of all, export the overtime clock-in as an excel**, and then open it.

2. In cell E3, enter the function =text(mod(c3-d3,1), "h hours m minutes"), and then click enter.

3. Where the mod is a time function, the actual off-duty time **c3-normal off-duty time d3, so that you can calculate how long you have been working, and then enter the following format to standardize the result.

4. When the carriage is entered, the normal overtime time has come out, if you want to operate in batches, you only need to double-click **, or drag the cross behind.

Count overtime hours, generally using excel functions can be done:

Before going to do statistics, we should first make the original table of the working time statistics table, and it is best to fill in the morning commuting time and afternoon commuting time separately when we do the basic table, as shown in the following figure:

<> insert a column of "Morning Time Difference" after the morning end time and a column "Afternoon Time Difference" after the afternoon end time

<> calculate the morning time difference first:

Insert the "min" function in the cell below "Morning Time Difference" and <>

<> subtract the max function after the min function, and fill in the value of the max function: number1: refer to the d3 cell; number2：

Fill in the specified morning work hours"8：30"(double quotation marks in English), drag the fill handle and pull it down.

<> calculate the afternoon time difference:

Insert the "min" function in the cell below "Afternoon Time Difference", <>

Subtract the max function after the min function, and fill in the value of the max function: number1: reference g3 cell; number2：

Fill in the specified afternoon working hours"13：15"(double quotation marks in English), drag the fill handle and pull it down.

Enter "Total Time" after "Afternoon Time Difference", enter the formula in J3: =F3+I3, press Enter to confirm, and drag the fill table to fill it.

<> enter or copy and paste the following formula in d2.

if(or(right(b2)=)c2,if(c2>8,floor(c2-8,,0))

Or. =if(or(right(b2)=)c2,(c2>8)*floor(c2-8,pull-down padding.

<> multiple pieces of data, you can pull down the formula.

c2:if(count(a2:b2)=2,(max(min(b2,/24)-max(a2,/24),)max(min(b2,/24)-max(a2,

Drop-down (Note: The schedule for this example is 9:00, 12:00, 13:00, and 18:00).