How to Calculate Overtime Hours in Excel Using IF Function

Get FREE Advanced Excel Exercises with Solutions!

Excel is a useful tool to perform any type of calculation. Therefore, users try to calculate overtime hours in Excel using the IF function. The IF function has its own syntax as:
IF (logical_test, [value_if_true], [value_if_false]).

Let’s say we have a dataset that contains employees’ total working time in hours and minutes. Now, we want to find the overtime hours (i.e., only hours or hours and minutes) using the IF function.

Dataset-How to Calculate Overtime Hours in Excel Using if Function

This article demonstrates multiple variants of the IF functions to calculate overtime hours in Excel.


4 Effective Examples to Calculate Overtime Hours in Excel Using IF Function

From the syntax, we know that the IF function takes a logical_test in its argument and performs the assigned operations depending on the outcomes of the test (i.e., TRUE or FALSE). Go through the following examples to get familiar yourself with multiple variants of the IF function.


Examples 1: Overtime Calculation Using IF and TIME Function with Custom Text

If users need to calculate overtime hours (i.e., hours and minutes) and want to place a Custom Text (i.e., No Overtime) where no overtime occurred, you can calculate working hours and Overtime Using Excel Formula such as a combination of IF and TIME function. Use the following formula in any adjacent cell (i.e., F6).

=IF(E6-TIME(8,0,0)>0,E6-TIME(8,0,0),"No Overtime")

In the formula, the IF function takes E6-TIME(8,0,0)>0 as its logical test. The test finds that E6-TIME(8,0,0) amounts to more than zero (0). Where the TIME function deducts the 8 hours from the time worked amounts. If the test results in TRUE, it returns the overtime; otherwise “No Overtime” text.

Example 1-How to Calculate Overtime Hours in Excel Using if Function

 🔺 Press ENTER then drag the Fill Handle to find the overtime hours and minutes.

Example 1 Outcome


Examples 2: Calculating Overtime Using IF and TIME Functions with Condition

Now, what if users want to consider overtime hours only greater than 1 as overtime hours. In that case, the previous formula needs a slight upgrade. Instead of the previous IF and TIME formula, use the below formula to calculate only the overtime hours of more than 1.

=IF(E7-TIME(8,0,0)>=TIME(1,0,0),E7-TIME(8,0,0),0)

The logical_test gets a slightly upgraded version of E7-TIME(8,0,0)>=TIME(1,0,0). Where the TIME function ensures only the overtime hours more than 1 are considered as overtime.

Example 2-How to Calculate Overtime Hours in Excel Using if Function

 🔺 Hit the ENTER key, then drag the Fill Handle to apply the formula to other cells as shown in the below picture.

Example 2 Outcome


Examples 3: IF Function with Direct Cell Reference in Overtime Calculation

Sometimes, users have regular working hours in a cell. And they can use this cell as a direct reference in the overtime calculation. As you can see, in the dataset we have the normal working hours in cell C4. We use that cell to insert the logical_test and calculate the overtime. Type the below formula in cell F7.

=IF(E7>$C$4,E7-$C$4,"N/A")

Example 3-How to Calculate Overtime Hours in Excel Using if Function

🔺 After the formula insertion, drag the Fill Handle to find the overtime hours (i.e., hours and minutes) in other cells.

Example 3 Outcome


Examples 4: IF Function without Cell Formatting in Overtime Calculation

In some cases, users don’t apply formatting or the cell values don’t have specific formatting.

In those cases, users need to find the General (Cell Format) value of 8 hours (i.e., 0.33) and use it in direct cell references to insert logical_tests or cell references.

=IF(E7>$C$4, (E7-$C$4)*24,0)

Multiplying time values (that are in General Format) by 24 results in hours.

Example 4-How to Calculate Overtime Hours in Excel Using if Function

🔺 Use the ENTER key to apply the formula. Then drag the Fill Handle to apply the formula in other cells as depicted in the picture below.

Example 4 Outcome


Download Excel Workbook


Conclusion

In this article, we demonstrate multiple variants of the IF function to calculate overtime hours in Excel using the IF function. We discuss the outcomes as well as cell formats in calculating overtime hours. We hope these above examples clarify your understanding regarding the calculation of overtime hours. Comment if you have further inquiries or have anything to add.


<< Go Back to Overtime | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

2 Comments
  1. Reply
    Mr Martyn Williets Dec 12, 2023 at 8:08 PM

    how do you make a personal time sheet that allows anytime before 6am and after 6pm is to be overtime,

    example;
    my normal hours are paid by the minute from 6am to 6pm
    my shift is min 10.5 hours per day take away unpaid break of 30 mins
    if i start before 6am then its overtime rates and again after 6pm is overtime rates
    how do i make a simple sheet so i can track my pay

    example 1

    05:37 start and 15:39 finish (06:00-05:37) = 00:23mins Overtime, (15:39-06:00)=10:09 Hours Normal pay 30mins Break)

    many thanks Martyn

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Dec 13, 2023 at 7:12 PM

      Hello MR MARTYN WILLIETS

      Thanks for reaching out and sharing your requirements. You wanted to create a personal payment tracker sheet. To create such a sheet, we can work with fields like Date, Start Time, End Time, Total Paid Hours, Overtime Hours, Overtime Pay, Normal Hours Pay, and Total Pay. Assuming you get $76.5 for overtime hours and $51 for regular hours. I am delighted to share how you can create a sheet to fulfil your goal.

      Overview of Personal Payment Tracker Sheet:

      Follow these steps:

      Step 1: Total Paid Hours
      Select cell E3 => Insert the following formula => Drag the Fill Handle icon to cell E10.

      =TEXT((D3-C3-TIME(0,30,0)),”h:mm”)

      Step 2: Overtime Hours
      Select cell F3 => Insert the formula below => Drag the Fill Handle icon to cell F10.

      =IF(OR(C3TIME(18,0,0)), TEXT((D3-C3-TIME(10,30,0)),”h:mm”), 0)

      Step 3: Overtime Pay
      Choose cell G3 => Insert the formula below => Drag the Fill Handle icon to cell G10.

      =IFERROR(TIMEVALUE(F3)*(76.5/0.041667),0)

      Step 4: Normal Hours Pay
      Choose cell H3 => Insert the formula below => Drag the Fill Handle icon to cell H10.

      =IFERROR(TIMEVALUE(E3)*(51/0.041667),0)

      Step 5: Total Pay
      Choose cell I3 => Insert the formula below => Drag the Fill Handle icon to cell I10.

      =G3+H3

      I am also attaching the solution workbook for better understanding. Hopefully, the idea will help you to reach your goal. Good luck.

      DOWNLOAD WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo