✦Template Printable Ideas Insights

Calculate Hours Worked: Excel Formula Guide

By

Ever found yourself manually calculating hours worked in Excel, wishing there was a quicker, more efficient way? Well, there is. In this guide, we'll explore a simple formula to save you time and effort. Let's dive right in.

Before we delve into the formula, let's ensure you have the right data. You'll need two columns: one for start times and another for end times. These can be in various formats, but for this formula, ensure they're consistent - either all date & time or all time only.

Understanding the Formula

The core of our calculation is the TIME function in Excel. This function converts start and end times into decimal hours, which can then be subtracted to find the duration.

How to Calculate Hours Worked in Excel
How to Calculate Hours Worked in Excel
the employee timesheet is displayed in this screenshot for employees to use on their workdays
the employee timesheet is displayed in this screenshot for employees to use on their workdays
calculate work hours
calculate work hours

Once you have the duration, you can use the SUM function to add up all the hours worked over a period.

Breaking Down the Formula

First, let's look at the TIME function syntax: =TIME(hour, minute, second). For example, if your start time is "09:00", and your end time is "18:00", the formula to find the duration would be:

Calculate Production Per Hour in Excel
Calculate Production Per Hour in Excel
Calculate work hours
Calculate work hours
107K views · 1.5K reactions | Calculating Worked Hours Using Formula!! #Excel | Excelling At Excel
107K views · 1.5K reactions | Calculating Worked Hours Using Formula!! #Excel | Excelling At Excel

=TIME(18,0,0) - TIME(9,0,0)

Applying the Formula to a Range

Now, let's apply this to a range of cells containing start and end times. Assume your start times are in column A (A2:A10), and end times in column B (B2:B10). In cell C2, enter the following formula:

=B2-TIME(A2,0,0)*1
Drag this formula down to C10

How to Calculate Hours Worked Minus Lunch Using Excel Formulas (4 Methods) - ExcelDemy
How to Calculate Hours Worked Minus Lunch Using Excel Formulas (4 Methods) - ExcelDemy
How To Count Or Calculate Hours Worked In Excel
How To Count Or Calculate Hours Worked In Excel
Time Sheet Calculator in Excel
Time Sheet Calculator in Excel
How to calculate Worked Hours in Excel #excel #exceltips
How to calculate Worked Hours in Excel #excel #exceltips
calculate hours worked in excel template
calculate hours worked in excel template
18K views · 603 reactions | This Excel Formula feels illegal to know! 🤯 In this tutorial you'll learn how to calculate working hours with breaks in Excel! #excel #spreadsheets #workhacks #accounting #exceltips | Excelbychris | Facebook
18K views · 603 reactions | This Excel Formula feels illegal to know! 🤯 In this tutorial you'll learn how to calculate working hours with breaks in Excel! #excel #spreadsheets #workhacks #accounting #exceltips | Excelbychris | Facebook

This will give you the duration in hours for each day. To find the total hours worked over a period, use the SUM function:

=SUM(C2:C10)

Handling Holidays and Absences

What if you want to exclude certain days, like holidays or absences? You can use conditional formatting to handle this. Let's assume any days to exclude are marked as "Holiday" or "Absent" in column D.

How to use the MOD function to calculate time values. 🤓
How to use the MOD function to calculate time values. 🤓
how to calculate working hours and pay
how to calculate working hours and pay
How To Calculate Total Work Hours Minus Lunch Time In Excel
How To Calculate Total Work Hours Minus Lunch Time In Excel

Conditional Formatting for Exclusions

1. Select cells C2 to C10 (your durations).
2. Right-click and select 'Conditional Formatting' > 'New Rule' > 'Use a formula to determine which cells to format'.
3. Enter the formula: =IF(D2="Holiday" OR D2="Absent",TRUE,FALSE)
4. Format these cells with a background color, e.g., light gray.

Now, any durations for excluded days will be grayed out, making it easy to see what's been excluded.

Removing Excluded Days from Total

To ensure these excluded days aren't included in your total, wrap your sum formula like this:

=SUM(IF(D2:D10<>"Holiday",IF(D2:D10<>"Absent",C2:C10)))

And there you have it! A simple yet powerful formula to calculate hours worked in Excel, even when excluding certain days. Now get back to maximizing efficiency and minimizing manual calculations.