How to Calculate Overtime Hours in Excel

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

Template

You can download the Template here – Download

RELATED ARTICLESMORE FROM AUTHOR