When you know how to calculate duration in Excel, tasks like time sheet preparation, project scheduling, budget planning, calculating overtime, managing tasks, employee assistance monitoring, etc. seem fairly easy.

Microsoft Excel stores and processes date and time in a unique way. Therefore, you must adopt a delicate approach to calculate time duration in this tool. If you’re not careful or you don’t know the right methods, you might end up creating a dataset or report that isn’t useful at all.

In this elaborate and step-by-step guide, you’ll learn all the convenient, easy-to-learn, and practical techniques to calculate time duration. Read the tutorial until the end and follow along with the methods.

ðŸ“’ **Read More**: 5 Ways to Get the Current Date or Time in Excel

## Using the Subtraction Operator

This is the easiest way to calculate the duration between start and end time values. However, the subtraction operator will work flawlessly only if you have formatted the inputs using the **Time** format in the **Number** formatting commands block of the **Home** tab.

ðŸ“’ **Read More**: 9 Ways to Format Time in Microsoft Excel

For example, in the dataset shown above, the time entries have been already formatted using the **Time** format code of Excel. Also, the time differences are within **24 hours**.

To calculate the duration, enter the following formula in `D2`

:

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

The above formula automatically does the following:

- Subtract the values in the
**Start Time**column from the**End Time**column. - If the
**End Time**is less than the**Start Time**, it reverses the values. - Converts fractional time value to
**24-hour format**. - Adds
`1`

to the**Start Time**values when the**End Time**is the next day.

Hit `Enter` to calculate the value in `D2`

.

Drag the fill handle down to calculate the remaining cell in the **Duration** column.

The resulting values are sufficient to calculate payroll or project time budgets. You can add the values to get a total duration or multiply them with hourly rates for payment purposes.

However, for reporting, it might not be appropriate to express time in decimals, like `14.74`

. Therefore, you can use the following formula to express the time duration in hours and minutes:

`=INT((IF(C2<B2, C2+1-B2, C2-B2)*24)) & " hh " & TEXT((IF(C2<B2, C2+1-B2, C2-B2)*24 - INT(IF(C2<B2, C2+1-B2, C2-B2)*24)) * 60, "0") & " mm"`

Find above a screenshot of the time duration calculation when using the extended formula.

A drawback of this method is that it can’t automatically account for time duration beyond 24 hours or for those values that extend to the next day. So, if the end date is the next day, you’ll get an incorrect figure. However, this issue can be tackled using the **IF** function, already shown in this exercise.

## Using the Excel NOW Function

Suppose, all the subtasks of a project are ongoing. You want to create an interim report to present the overall progress of the said project. In such a case, you’ll need to subtract the start time values from the current time to get the elapsed time values. Here, you can use the **NOW** function along with the subtraction method mentioned earlier.

Your working dataset’s structure could be similar to the one shown above.

In the **End Time** column, enter this formula for all cells containing values in the **Start Time** column.

`=NOW()`

Hit `Enter` to populate the current date.

Now, use the following formula to get the total time elapsed irrespective of working hours, weekends, and holidays, for the subtasks in `D2`

:

`=INT((IF(C2<B2, C2+1-B2, C2-B2)*24)) & " h " & TEXT((IF(C2<B2, C2+1-B2, C2-B2)*24 - INT(IF(C2<B2, C2+1-B2, C2-B2)*24)) * 60, "0") & " m"`

Hit `Enter` to calculate the formula.

Select the fill handle and drag it down to copy the formula to the rest of the cells as necessary.

If you only want to count the allowed working hours, say **9 hours a day** for all the subtasks except weekends, then use the following formula:

```
=TEXT((NETWORKDAYS(B2, NOW()) * 9) + (MOD(NOW() - B2, 1) * 24), "0") & " h " & TEXT((MOD(NOW() - B2, 1) * 24), "0") & " m"
```

## Calculating Duration in One Unit

Suppose, you only want to calculate duration in a specific time unit, like hour, minute, or second. For this, you can use the following functions according to your unit preference:

**HOUR:**To calculate the complete hours between the start and end time.**MINUTE:**To get the time difference in minutes, excluding hours and seconds.**SECOND:**Calculate only the second difference between two-time stamps. It will ignore the hour and minute differences.

For example, in the above dataset, you want to calculate the duration values in hours.

Simply, enter the following formula in `D2`

to get the elapsed hour:

`=HOUR(C2)-HOUR(B2)`

Hit `Enter` to calculate the formula cell.

However, this formula will generate incorrect values if the **End Time** is in the next day.

Therefore, you can use the following modified formula instead:

`=IF(HOUR(C2) < HOUR(B2), HOUR(C2) + 24 - HOUR(B2), HOUR(C2) - HOUR(B2))`

This extended formula will automatically adjust the following:

**AM/PM handling****Day change****Negative hours**

## Using the Excel MOD Function for Durations Over 24 Hours

When you expect a time duration **beyond 24 hours**, you can also use a simple **MOD** function to calculate the time duration in Excel.

For example, look at the given dataset above.

Here, to calculate the elapsed time, copy and paste the following formula into `D2`

:

`=MOD(C2 - B2 + 1, 24)`

Press `Enter` to calculate the cell.

Now, you can use the fill handle to copy the same formula in all the cells below `D2`

.

## Using the Excel DATEDIF Function (For Days, Months, Years)

If the project spans years, you might want to get duration outputs in years, months, and days format. Here, the **DATEDIF** function will come in handy.

On some Excel editions, you might not see the function in the suggestion box. However, the formula will still work.

For example, the Excel for Microsoft 365 desktop app won’t show the function in the in-cell context menu.

Now, let’s see how to use this formula below.

The dataset of the current exercise is given above.

You can enter the following formula syntax in `D2`

and press `Enter` to calculate the time duration for the first cell of the **Duration** column.

`=DATEDIF(B2, C2, "Y") & " Years " & DATEDIF(B2, C2, "YM") & " Months " & DATEDIF(B2, C2, "MD") & " Days" `

Now, **use the fill handle** and drag it down to calculate the rest of the cells in **column D**.

## Using Power Query Editor

So far, you’ve seen how to extract the time duration from start and end time when your data is clean and organized.

However, if you’re importing a dataset from an external server or a project management software, the time entries might not be organized or formatted according to Excel.

ðŸ“’ **Read More**: The Complete Guide to Power Query

In such scenarios, you can first import the dataset to **Power Query Editor**, structure your data, transform it, extract the time durations you seek, and export a clean dataset to an Excel worksheet.

To import your dataset, go to the **Data** tab on Excel and click on the **Get Data** drop-down menu. It should be within the **Get & Transform Data** block.

Now, hover the cursor over the primary data source, like **From Database**, **From Azure**, etc.

An overflow menu will open. Now, you can select the actual database from options like, **From Oracle Database**, **From IBM Db2 Database**, and more.

In the current exercise, I exported the above dataset to **Power Query Editor** using the **From Table/Range** command in the **Get & Transform Data** commands block.

When you’re importing from the existing worksheet, click **OK** on the **Create Table** dialog box.

You should now see your dataset in **Power Query** as shown above. It’s like a **free-floating table** above the existing Excel worksheet. You won’t be able to interact with the source worksheet when Power Query is open.

Firstly, you should populate the names in the **Name** column for all the time entries. To do that, **right-click** on the **Name** column and hover the mouse cursor over the **Fill** menu in the context menu.

Select the **Down** option in the overflow menu that opens on the right.

Now, to get a column for the time duration, click on the **Clock Out** column header. Press the `Ctrl` key and then select the **Clock In** column header.

Navigate to the **Add Column** tab. Click on the **Time** drop-down menu in the **From Date & Time** block. Choose **Subtract** from the context menu that opens.

The new **Subtraction** column header will be created. The values in this column are the duration values you’ve been looking for. The entries are in the `Days.Hours:Minutes:Seconds`

format.

You can rename the **Subtraction** column to **Time Duration** by **double-clicking** on the column header.

You can now export your Power Query table to an Excel worksheet if you don’t want any more modifications. Follow the steps from which I’ve explained the **Close & Load To** action below.

If you only need the hour values with decimal places, select the **Time Duration** column, and go to the **Duration** drop-down in the **From Date & Time** commands block of the **Add Column** tab.

Select the **Total Hours** option in the context menu. You should now only see the hour values. There might be multiple decimal places as well.

To minimize the decimals to just one digit after the integer, select the **Total Hours** column, go to the **Transform** tab on the top, and click on the **Rounding** drop-down menu in the **Number Column** commands block.

Click on the **Round** option. Type `1`

in the **Round dialog** and click **OK**.

The **Total Hours** column will now contain hour values with one decimal place.

Now, delete unwanted columns from the Power Query table by **selecting the column headers** while pressing the `Ctrl` key.

**Right-click** and choose **Remove Columns** from the context menu.

Rename the **Total Hours** column to **Time Duration**.

Click on the **File** tab. Choose **Close & Load To** from the context menu.

You should see the **Import Data** dialog box. Select the **Existing worksheet** radio button. Now, choose a destination cell on the destination worksheet.

Find above a screenshot showing the **Time Duration** column created and imported from **Power Query**.

## Using Excel VBA

Excel VBA lets you automate the time duration calculation. I’ll share a simple VBA script so you can create your own VBA macro to compute elapsed time.

However, check out this Excel tutorial first to learn the process of setting up a macro in Excel VBA:

ðŸ“’ **Read More**: How To Use The VBA Code You Find Online

### Calculate Duration in Hours

The following script will allow you to compute duration in hours with up to two decimal places. The input dataset should be in the **Time** format.

```
Sub CalculateTimeDuration()
Dim ws As Worksheet
Dim startTimeCol As Range
Dim endTimeCol As Range
Dim destCol As Range
Dim i As Long
Dim startTime As Date
Dim endTime As Date
Dim duration As Double
Set ws = ActiveSheet
On Error Resume Next
Set startTimeCol = Application.InputBox("Select the start time column", Type:=8)
Set endTimeCol = Application.InputBox("Select the end time column", Type:=8)
Set destCol = Application.InputBox("Select the destination column", Type:=8)
On Error GoTo 0
If startTimeCol Is Nothing Or endTimeCol Is Nothing Or destCol Is Nothing Then
MsgBox "Column selection canceled. Please try again.", vbExclamation
Exit Sub
End If
For i = 2 To ws.Cells(Rows.Count, startTimeCol.Column).End(xlUp).Row
startTime = ws.Cells(i, startTimeCol.Column).Value
endTime = ws.Cells(i, endTimeCol.Column).Value
duration = endTime - startTime
If duration < 0 Then duration = duration + 1
ws.Cells(i, destCol.Column).Value = Format(duration * 24, "0.0")
Next i
MsgBox "Time duration calculated and placed in the destination column.", vbInformation
End Sub
```

After creating a macro, press `Alt` + `F8` to access the **Macro** dialog. There, select the **CalculateTimeDuration** macro and hit **Run**.

Excel will show the following pop-up boxes to guide you through the rest of the process:

- An input box you can use to select the
**Start Time**column, except the column header.

- Another input box is to choose the
**End Time**without the column header text.

- A third input box will be there so you can choose the destination cell range.

If you enter the right values, Excel VBA will compute the duration values as shown above.

## Conclusions

Now you know how to calculate duration in Excel using various function-based methods, like Subtraction, NOW, HOUR, MOD, DATEDIF, and more.

Here, you’ve also learned and practiced time duration calculation in Power Query Editor.

Moreover, you’ve also acquired the skill to use Excel VBA for automating time duration calculations in Microsoft Excel.

If the article helped you calculate elapsed time values, you can acknowledge it below. If you’ve got any suggestions, share those in your comments.

## 0 Comments