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.

## 0 Comments