Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Learn How to Calculate Overtime in Excel

The total worked hours and overtime hours calculations are dependent on employees’ start time and finish time. So, we can easily keep track of these times to calculate overtime by comparing the total hours worked with the regular hour threshold. This article will step through the process of calculating overtime

Figure 1. Overtime Calculation in Excel

Setting Up the Spreadsheet

First of all, we need to set up our spreadsheet to track the start time and finish time of employees’ shift, such as column B and C. This time tracking is important to make calculations for worked hours, regular hours and overtime hours, such as column D, E and F respectively in our example.

Figure 2. Setting Up Spreadsheet

Calculating Worked Hours

First, we need to calculate the total worked hours based on employees’ shift start and finish timings. This is done by simply subtracting start time from the finish time. As Excel natively handles the time as a fraction of the day, therefore we need to convert this fraction to decimal hours to make worked hours calculations simple and easy by multiplying the difference of start and finish timings by 24, such as;

=(Finish_time - Start_time)*24

And if there is a case, where shift crosses the midnight then we also need to calculate elapsed time in total worked hours calculation. In this case, we need to make some adjustments in basic above worked hours calculation formula, such as;

=(Finish_time - Start_time+(Finish_time < Start_time))*24

So, the final formula to calculate “Worked Hours” in this example would be as follows as entered in cell D2;

=(C2-B2+(C2<B2))*24

Figure 3. The formula to Calculate Worked Hours

Calculating Regular Hours

The regular hours’ calculation is essential to calculate overtime in Excel and it is done by comparing defined regular hours, say 8 hours, with worked hours calculated in column D, whichever is smaller. For this, we use the MIN function in column E to calculate “Regular Hours” by entering the following formula in cell E2;

=MIN(8,D2)

Figure 4. Calculating Regular Hours

 Calculating Overtime Hours

After calculating worked hours and regular hours, now we are able to calculate overtime in Excel. This is simply worked hours minus regular hours or applying the MAX formula in cell F2, such as;

=MAX(0,$D2-$E2)

Figure 5. Calculating Overtime Hours

Now select the cells D2, E2, and F2 and drag down this selection to apply this set of formulas to other rows in the spreadsheet as shown below;

Figure 6. Dragging the Formulas Set

Figure 7. Final Preview of Calculations

Worked Hours Calculation with Break Times in Shift

If we incorporate break timings in our spreadsheet, such as “Lunch Start” and “Lunch End” timings, then we need to adjust the worked hours formula to subtract these break hours from worked hours in order to correctly calculate overtime in Excel. So, the formula for worked hours would be as follows:

=((E2-B2+(E2<B2))-(D2-C2))*24

Figure 8. Calculating Worked Hours by Subtracting Break Hours

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Solution examples
Hi, i need one formula, for example in salary level 200000 to 300000 is coming under 6B 300000 to 450000 is coming under 6A 450000 to 600000 is coming under 5A Now if salary is 280000 so which formula i can use to find their Band lavel
Solved by E. F. in 35 mins
I have a list of cost prices in one column and i would like to know what formula to use to add a percentage value. E.g if my cost price is just £1 i would like to create the sell price to be perhaps £3, but if my cost price is £1000, i would like the sell price to perhaps be £1,100 (not £3,000).
Solved by M. Y. in 60 mins
If I have a sheet that pulls data from another sheet. How can I compare if the right data is filled in, after the fact?
Solved by Z. Y. in 43 mins
I want a formula (or combination of formulas) to look up the matching values(full name) within one column and based on those matching look in 2nd and 3rd column (email in and email out in last 6mo) and indicate which one is the most/highest. If there is a tie, then look in 4th and 5th column (email in and out in last year). It could be indicated by a yes or number to say that that name.
Solved by E. H. in 40 mins
I am having issues with max If function array formula using Is number / Find to find data
Solved by D. C. in 22 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc