How to Calculate Overtime Hours in Excel
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.
Template
You can download the Template here – Download
Further reading: Basic concepts Getting started with Excel Cell References
