Calculate overtime with a formula in excel

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.

3. Click "Column", pop up the tab, select "Fixed Width", and then click the "Next" button.

4. In the text sorting wizard, adjust the position of the list appropriately so that each time point is separated, and then click the "Next" button.

5. The list is divided separately.

6. According to the above steps, on the corresponding side of the column "overtime", as shown in the figure, enter the function "=text(mod(c2-b2,1),"."h hours m minutes"and click Enter to display the D2 content.

7. When the cross-shaped cursor appears below the display result bar, pull down or double-click to fill in all the content.

If the clock-in time is 18:31, it will be counted from 19:00."

For example, if B1 is off duty and A1 is the start time of overtime, then the number of overtime hours:

floor(b1-a1,,60,0)

If the time data is in B2, the format is the standard time format:

int(mod(b2,1)*24*10 5)*5 10 If you want to use the off-duty time (if in C2) to calculate directly:

mod(b2,1)*24-int(mod(b2,1)*24*10/5)*5/10

If you also want to round up, you need to refer to the clock-in time to handle the clock-out time.

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.

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.

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.

<> 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).