How to Get the Number of Hours Between Two Times in Excel: 6 Best Ways

Want to know how many hours are there between two times? Learn how to get the number of hours between two times in Excel.

Excel lets you perform a variety of tasks, one of which is calculating the number of hours between two times. While this calculation is manually possible, it would take some time, not to mention the chance of human error.

Also, you must be extra careful about time formats like AM and PM. For these reasons, Excel users prefer to get the hours between two times quickly and efficiently using the application.

Reasons to Get the Number of Hours Between Two Times

1. Calculating Shift Timing

If a company operates in shifts, the management needs to get the accurate duration between shift start and end times to manage employee schedules. Hence they need to know the hours between two times to comply with labor regulations.

2. Managing Time for a Project 

For project management, it is important to monitor the time needed to complete specific tasks or milestones.

By calculating the time between start and end times, you can easily find out the time taken to complete a task or project and evaluate project progress.

3. Analyzing Efficiency

Time calculation is also helpful in finding out the efficiency of your team members. It also tells you if there are any inefficiencies or bottlenecks in your process.

By gaining insights into your workflow, you can precisely allocate resources and optimize operations.

4. Tracking Billable Hours

Many businesses and individuals need to track billable hours for consultants or working hours for employees. This hour tracking helps them by recording the accurate timing for payroll or billing.

5. Analyzing Time-based Data

In fields like sales, marketing, production, and logistics, time-based metrics are the driving force. By getting the time duration between two timestamps, you can use the data for trend analysis and performance evaluation.

6. Service Level Agreements

If a company has SLAs where response or resolution times for certain activities are defined clearly, calculating the hours between the issue report and resolution can ensure its adherence.

Now, find below various methods to calculate the number of hours between two times in Excel:

Get Hours Between Time with the Subtraction operator

The easiest way to get the hours between two times in an Excel worksheet is by using the subtraction operator. Here are the steps to show you how:

1. Select a cell where you want the hour value.
2. Enter the following formula and hit Enter:

=B2-A2

3. In the above formula, B2 is the end time, and A2 is the start time. Adjust the cell reference according to your own dataset.

4. The resultant value is 8:00 AM which indicates 8 hours.

5. Since it’s not quite clear, you can manually format the value by pressing Ctrl + 1.
6. On the Format Cells dialog box, go to Category > Time.
7. There, choose a Type that matches the XX:XX format, for example, 13:30.
8. Click OK and the 8:00 AM value will turn into 8:00, which you can easily read as hours.

You can use the fill handle or copy the formula and paste it below for other values in your data set.

Get Hours Between Time with the IF Function

Calculating the hours between two times where the start time is earlier than the end time is easy.

Issues arrive when you need to calculate the hours between two times where the end time is earlier than the start time. It means the time span of the work has crossed midnight. Such instances are easily found when people work nightshift.

Excel is unable to handle negative time values so it returns an error of indefinite # keys. To fix this, you can use an IF function to instruct Excel on how to handle such situations.

Here’s a complete step-by-step breakdown of the process:

1. Highlight the cell where you want to perform the calculation.
2. Enter the following formula into the cell and press Enter:

=IF((B2-A2)<0,1-(A2-B2),(B2-A2))

3. In this formula, B2 is the start time, and A2 is the end time. You can modify the references as needed.
4. The resulting value is 3:00 PM.

5. Apply the XX:XX time format by following the method explained earlier.
6. The value should now change to 15:00.

This is the hour value you’ve been looking for. The advantage of this formula is, it will follow the normal subtraction method for earlier start time and later end time.

However, when the end time becomes smaller than the start time, Excel will perform the special calculation which considers the work timing crossed midnight.

Get Hours Between Time with the TEXT Function

Find below the steps to use the TEXT function to calculate hours between an end date and a start date:

1. Go to the cell where you need this value and enter the following formula:

=TEXT(B2-A2,"hh:mm")

2. Don’t forget to modify the cell references in the formula according to your dataset.
3. Hit Enter to get the hour value.

If you want text strings in place of calculated hours, copy the resulting cell and paste it into the destination by pressing Ctrl + Alt + V. On the Paste Special dialog box, choose Values and click OK to get text strings.

Get Hours Between a Start Time and Current Time

Suppose, there are several start times for a task in your worksheet. You’d like to find out how many hours elapsed until now since those start times.

You can use the NOW function by following the steps outlined below:

1. Organize your dataset as shown in the above example by creating columns for Started by, Started at, and Hours until now.
2. Select C2 as the target cell for the calculation and enter the following formula:

=TEXT(NOW()-B2,"hh:mm")

3. Hit Enter and you get the hourly difference between the first start time.

4. Now, drag the fill handle until C6 to get the hourly values for the rest of the start times.

Get Hours Between Time with a VBA Script

Are you looking to automate the whole process of calculating the hours and minutes between two timestamps? Do you want to make an automated timesheet for your employees or contractors?

For these, you can use Excel VBA. Find below the code and steps to follow:

1. Go to the Excel worksheet where you got the start time and end time columns.
2. Now, hit the Alt + F11 together to bring up the VBA Editor.
3. There, click Insert and choose Module.
4. Inside the new blank module, copy and paste the following VAB script:

Sub CalculateDuration()
    Dim startRange As Range
    Dim endRange As Range
    Dim durationRange As Range
    Dim startCell As Range
    Dim endCell As Range
    Dim durationCell As Range

    ' Set the ranges for start time, end time, and duration
    Set startRange = Range("B2:B6")
    Set endRange = Range("C2:C6")
    Set durationRange = Range("D2:D6")

    ' Loop through each row in the ranges
    For Each startCell In startRange
        ' Get the corresponding end time and duration cells
        Set endCell = endRange.Cells(startCell.Row - startRange.Row + 1)
        Set durationCell = durationRange.Cells(startCell.Row - startRange.Row + 1)

        ' Calculate the duration in hours and minutes
        durationCell.Value = endCell.Value - startCell.Value

        ' Format the duration as hours and minutes
        durationCell.NumberFormat = "h:mm"
    Next startCell
End Sub

5. In the above script, you must modify the cell references according to your own dataset as outlined below:
   • "B2:B6": cell range for the start time of the shift
   • "C2:C6": cell range referring to the end times of the shift
   • "D2:D6": cell range where Excel VBA will populate the results
6. Now, click the Save button and click Yes on the warning message that pops up.
7. Click the close button to close the Excel VBA Editor tool.

8. Now, hit the Alt + F8 buttons together to open the Macro dialog box.
9. There, click the CalculateDuration macro and hit Run.
10. You get your hour and minute values in the designated cell range, like D2:D6 in this tutorial.

Get Hours Between Time with Office Scripts

You can also use Excel Automate, an advanced scripting tool that works on both online and desktop Excel applications.

However, Microsoft has restricted access to this tool only to Microsoft 365 subscribers with Standard Business or better subscriptions.

Open Excel on the web or on the PC. Do you see the Automate tab? If yes, that’s great! You can use this automation tool for greater advantage than Excel VBA.

Now, find below the steps and codes to automate the calculation to get the numbers of hours between two times in Excel:

1. Go to the Automate tab and click New Script inside the Scripting Tools block.
2. The Code Editor will open on the right side of the worksheet.
3. Delete all the codes inside the Code Editor.

4. Now, copy and paste the following Office Scripts code inside the Code Editor:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range D1:D2 on selectedSheet
	selectedSheet.getRange("D1:D2").setFormulasLocal([["Hours"],["=C2-B2"]]);
	// Auto fill range
	selectedSheet.getRange("D2").autoFill("D2:D6", ExcelScript.AutoFillType.fillDefault);
	// Set number format for range D2:D6 on selectedSheet
	selectedSheet.getRange("D2:D6").setNumberFormatLocal("h:mm;@");
}

In the above code, D1:D2, D2, and D2:D6 are the cell range references for the destination. Similarly, C2 refers to the end date and B2 refers to the start date.

So, modify these values according to your own dataset. Once the customization is done, continue with these steps:

5. Click the Save script button.
6. Now, hit the Run button.

7. You’ll see hourly data populate inside the destination column, which is D in this tutorial.

Conclusion

You might want to know the number of hours between two times for various reasons, such as calculating the remaining hours of a shift or measuring the efficiency of your team members in completing a project.

If you use Excel for personal or business purposes, you can also use this software to calculate the difference between two times.

Follow these methods mentioned in this article, and you’ll learn how to get the number of hours between two times in Excel.

Which of these approaches is the easiest? Tell us in the comment. If you want, you can also check out the methods to calculate business days left in a year.