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.