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.

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

**Table of Contents**Expand

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

** **

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

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

** **

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

**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")`

** **

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

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

** **

**🔺 **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.

**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**

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

Hello

MR MARTYN WILLIETSThanks 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, andTotal Pay. Assuming you get$76.5for overtime hours and$51for 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 HoursSelect cell

E3=> Insert the following formula => Drag theFill Handleicon to cellE10.Step 2: Overtime HoursSelect cell

F3=> Insert the formula below => Drag theFill Handleicon to cellF10.Step 3: Overtime PayChoose cell

G3=> Insert the formula below => Drag theFill Handleicon to cellG10.Step 4: Normal Hours PayChoose cell

H3=> Insert the formula below => Drag theFill Handleicon to cellH10.Step 5: Total PayChoose cell

I3=> Insert the formula below => Drag theFill Handleicon to cellI10.I am also attaching the solution workbook for better understanding. Hopefully, the idea will help you to reach your goal. Good luck.

DOWNLOAD WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemy