The calculation of work overtime is dependent on knowing when an employee starts and when he or she finished for the day. Let’s start with laying out the data:

data table

1. Click under Total (F2) (1), and type in =(E6-B6 + (E6<B6))*24 (2).

click under total

2. Click on G6 (under worked hours) (1), and type =((E6-B6+(E6<B6))-(D6-C6+(D6<C6)))*24

click under worked hours

3. Add Regular Hours

click under Regular Hours

4. Click on H6 (under regular hours) (1), and type =Min(E2;G6), then press enter.

min function

5. Click on I6 (under Overtime Hours) (1), and type =MAX(0;G6-$E$2), then press enter.

max function

6. Mark from F6 to I6 (1), and double click on the small square (2).

mark data

Note: If there is anything that does not show correctly, you should right-click on that cell, choose format cells, and then click on number, and finally press okay.

This is how to Calculate Overtime Hours in Excel.