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:
1. Click under Total (F2) (1), and type in =(E6-B6 + (E6<B6))*24 (2).
2. Click on G6 (under worked hours) (1), and type =((E6-B6+(E6<B6))-(D6-C6+(D6<C6)))*24
3. Add Regular Hours
4. Click on H6 (under regular hours) (1), and type =Min(E2;G6), then press enter.
5. Click on I6 (under Overtime Hours) (1), and type =MAX(0;G6-$E$2), then press enter.
6. Mark from F6 to I6 (1), and double click on the small square (2).
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.