Wondering how to calculate hours worked in Excel? You’ve reached the best resource to learn from. So, tag along!

Calculating time values in Microsoft Excel can be a bit tricky, especially if you’re new to this specific spreadsheet skill. Excel is an excellent tool for managing time entries and calculating time differences, whether for determining someone’s salary or tracking hours worked on a project—but only if you know the correct operators, functions, and tools to use.

I’ve tested various methods for calculating working hours in Excel, and the ones outlined in this insightful Microsoft Excel tutorial are essential skills to know. Let’s get started!

Time Calculations in Excel

Excel handles time calculations by treating time records as fractional parts of a day. Each day is represented as 1, so time values are fractions of 1. For example, look at the list below:

• 1 hour is equivalent to 1/24 of a day.
• 1 minute is equal to 1/(24 * 60) of a day.
• 1 second is equivalent to 1/(24 * 60 * 60) of a day.

Therefore, if you enter a start time and an end time in two different cells you can apply simple arithmetic operators, like addition and subtraction to derive the time difference. However, you must appropriately format time values, like HH:MM, HH:MM:SS, or MM:SS.

Now that you’ve learned the basics of Excel’s time entry calculation process, let’s explore various ways to calculate working hours.

You might also want to check out the following ways to work with time data in Microsoft Excel:

📒 Read More: 4 Ways to Add Time in Microsoft Excel

📒 Read More: 6 Ways to Remove Time from Dates in Microsoft Excel

📒 Read More: 5 Ways to Get the Current Date or Time in Excel

Calculate Work Hours Using a Basic Formula

Find above an example of a timesheet dataset in the most basic structure. In this database of time records, none of the time differences go beyond 24 hours or the present date. So, calculating the hours worked from this dataset is super easy using the subtraction operator to subtract time.

Let’s say, you want the hours worked value in column C, cell C3 for the first record.

Select the destination cell and enter the following formula into it. Hit the Enter key on the keyboard to calculate.

=C3-B3

You must customize the formula syntax according to your own dataset. Here, C3 is the end time and B3 is the start time, where C3 must always be larger than B3.

Now, select C3 again, and drag the fill handle down until C7 to calculate the duration of the hours worked for the remaining time entries.

Due to the automatic Time formatting style, you’ll see AM or PM with the subtracted values.

To remove this Time formatting and show the worked hours in HH:MM format, select the C3:C7 cell range, press Ctrl + 1, and click on the Time category in the Format Cells dialog box.

Now, select the 13:30 formatting code below the Type field on the right side.

Click OK to apply the changes.

Excel will now show the hours worked in hours and minutes format that you can easily use in successive calculations for payroll, project costing, shift scheduling optimization, and so on.

One downside of this basic formula is it can’t hours clocked effectively if the end time goes beyond 11:59 PM.

When your dataset contains end-time entries, like 1:15:00 AM or 02:45:00 AM, and start times, like 9:00:00 AM, you’ll need to add 1 to the subtracted value to get an accurate worktime calculation.

For example, refer to the example dataset shown above.

If you wish to calculate the clocked employee hours in I3 by subtracting G3 from H3, you’ll need to use this formula:

=(G3-F3)+1

Don’t forget to modify the cell references in the formula syntax to fit it for your own dataset.

Press Enter to calculate the cell.

Use the fill handle to copy the same formula in the rest of the cells of the column I.

Even at this stage, you won’t get the correct worked hours format.

Use the Format Cells dialog box to apply the HH:MM Time formatting to the output cells and Excel will show the results appropriately.

Handling Overnight Shifts in Excel

Sometimes, your dataset may not easily be clean enough to distinguish between time spans within and beyond 24 hours. In such cases, where you need to handle a mix of time entries to calculate work hours, you can use the IF function. It effectively manages work times that span both within and beyond 24 hours.

For instance, if you’ve got an Excel dataset like the one shown above containing a mix of Time Out entries that go beyond the present date. Let me show you how to create and use a simple IF function to calculate working hours dynamically.

Select the destination cell, like C2, and enter this formula:

=(IF(B2<A2, B2+1, B2) - A2) * 24  

This formula adds 1 to B2 (Time Out) before subtracting A2 (Time In). Also, the final result is multiplied by 24 to show a decimal value instead of the default 12H Time format.

Customize the formula by taking into account your dataset’s Time Out and Time In entries.

Hit Enter to calculate hours worked using the IF function.

Drag the fill handle down until C6 to replicate the same formula and auto-calculate the selected cells.

Excel will show the shift hours of the employees efficiently, even if a few of them have worked overnight.

Calculate Hours Worked Using the SUM Function

The SUM function lets you quickly count the total worked hours for one employee. It could be for a week, the whole month, or for a list of projects in a week, and so on.

You can organize your worksheet as shown above.

Now, if you want to get the total clocked hours in B11, enter the following formula into the cell:

=(SUM(C3:C9)-SUM(B3:B9))*24  

In the above formula, you’re summing up the Time Out entries or the column C and Time In entries or the column B.

Then, you subtract the sum of Time In from the sum of Time Out. Finally, multiply the output value by 24 to get an output in decimal for total worked hours.

So, before using the formula syntax customize it according to your own dataset.

Now, hit Enter to calculate B11.

If the number formatting of B11 is General, the clocked hours will be 64.5 hours.

However, you could also get a value, like 12:00:00 PM. That’s because the cell is in the Time formatting mode.

Change that to General to see the correct output.

Some of the Time Out entries of your timesheet could extend to the next day. In that case, you need to use the SUMPRODUCT function.

Look at the sample dataset shown above. Cells, like H3, H4, etc., have time entries beyond 11:59 PM.

For a mixed dataset like this, use the following formula in G11 to calculate the worked hours in decimal values:

=SUMPRODUCT((H3:H9 - G3:G9 + (H3:H9 < G3:G9)) * 24)  

This formula calculates the total difference in time (in hours) between two sets of time values, accounting for instances where the end time is earlier than the start time (indicating a time span that crosses midnight).

The cell ranges H3:H9 and G3:G9 represent Time Out and Time In cell ranges respectively. You can modify these cell ranges by referring to your own dataset.

Hit Enter to calculate the cell.

Excel will compute and show the total clocked hours.

Overtime Calculations in Excel

Suppose you want to calculate the overtime hours worked by your employees by subtracting the standard 8-hour workday.

For practice, you can create a dataset that resembles the one given above.

In C2, enter the following formula:

=IF(B2 < A2, (B2 + 1) - A2, B2 - A2) * 24 - D$2

In this formula, B2 and A2 represent the Time Out and Time In entries respectively. I’ve wrapped the formula in IF to automatically account for those Time Out entries that go beyond the 11:59 PM mark.

Also, I’ve multiplied the output value by 24 to convert the time difference to a decimal value. Finally, I’ve deducted the standard working hours in decimal to get the overtime.

You need to modify the cell references, like B2, A2, and D2, according to your own dataset.

Press Enter on the keyboard to get the overtime value.

Click on the fill handle of C2 and drag it down until C8 to get the overtime values for the rest of the rows.

Excel will show the overtime hours in decimal values.

You can now directly multiply these values with the pay rate to create the payroll.

Calculate Hours Worked Using Power Query

Power Query allows you to compute the hours worked values in a large dataset that might not fit into an Excel worksheet. Also, as you load more data in an Excel workbook, it becomes slower while Power Query efficiently handles big datasets without compromising on performance.

There are two ways to import timesheet data in Power Query Editor. Firstly, you can navigate to the Data tab and click on the Get Data command.

📒 Read More: How Many Rows Can Excel Handle?

Now, hover your mouse cursor over the context menu items, like From Database, From File, From Azure, etc.

Then, you’ll see more data source options, like From SQL Server Database, From Azure SQL Database, and so on.

Choose any of these options to import external datasets to Power Query.

Secondly, if you wish to export a dataset from the current worksheet to the Power Query, select the source cell range.

Click on the From Table/Range command in the Data tab.

Select OK on the Create Table dialog.

You should now see your data in the Power Query.

The Time In and Time Out columns might show fractional values instead of an appropriate time. So, select these columns, go to the Transform tab, and click on the Data Type drop-down list in the Any Column block.

Select Time from the context menu.

Now, to handle Time Out entries that go beyond 11:59 PM, create an Adjusted Time Out column by clicking on the Add Column tab and choosing Custom Column from the ribbon menu.

On the wizard that follows, enter Adjusted Time Out in the New column name field.

Enter the following formula into the Custom column formula field:

let
    TimeInAsDateTime = #datetime(2000, 1, 1, Time.Hour([Time In]), Time.Minute([Time In]), Time.Second([Time In])),
    TimeOutAsDateTime = #datetime(2000, 1, 1, Time.Hour([Time Out]), Time.Minute([Time Out]), Time.Second([Time Out])),
    AdjustedTimeOut = if TimeOutAsDateTime < TimeInAsDateTime 
                      then TimeOutAsDateTime + #duration(1, 0, 0, 0) 
                      else TimeOutAsDateTime
in
    AdjustedTimeOut

This formula will only work if the referred columns in the syntax match your Power Query Editor’s columns. So, you can probably rename your own dataset with the ones in this formula or edit the column references in the formula.

Click OK to add the new column.

Now, use the following Power Query formula to create the Adjusted Time In column:

#datetime(2000, 1, 1, Time.Hour([Time In]), Time.Minute([Time In]), Time.Second([Time In]))

A new Adjusted Time In column will show up.

Also, follow the same steps mentioned above to create the new Custom Column called the Hours Worked. Use the following formula for the clocked-hour calculation:

Duration.TotalHours([Adjusted Time Out]-[Adjusted Time In])

Congratulations! You’ve successfully derived working hours from the given time entries in Power Query.

Click the File tab and choose Close & Load To from the context menu.

Select the Existing worksheet option in the Import Data dialog and select a destination cell in the worksheet.

This is how you get the hours worked data in your active worksheet.

Conclusions

Now you know how to calculate hours worked in Excel using a basic subtraction formula, the IF function, SUM function, and SUMPRODUCT function.

Also, you’ve learned how to use Power Query to compute working hours from a large dataset.

Finally, you’ve seen how to handle the Time Out entries going beyond the same day or 11:59 PM.

You can use the comment box to share the method you liked the most. Also, if you know any better approach to calculating working hours in Excel, mention that in your comment.