The calculation of work overtime is dependent on knowing when an employee starts and when he or she finishes for the day.
Let’s start by 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).
If there is anything that does not show correctly, you should right-click on that cell, choose Format Cells, and then click on the number, and finally press Okay.
This is how to Calculate Overtime Hours in Excel.