When you work on something like a timesheet in Microsoft Excel, you may need to get the difference between times. For instance, you may enter start time and end time where you need to see the number of hours worked.
We’ll show you a few ways to find the difference between two times in Excel for project tracking, a work timesheet, or an employee schedule.
Before You Begin, Format the Times
To correctly use the functions and formulas described here, be sure to use one of Excel’s time formats for the time values in your cells.
Either select Time in the Number drop-down box on the Home tab or use the Format Cells dialog box shown below.
Use the Time Functions for Basic Results
The simplest way to get the hours, minutes, or seconds between two times is using the same named functions in Excel: HOUR, MINUTE, and SECOND.
The syntax for each is the same with the function name and subtracted cells in parentheses. Let’s look at an example of each.
To get the total hours between the times in cells B2 and B1, you’d use this formula:
=HOUR(B2-B1)
For only the number of minutes between the times in cells B2 and B1, you’d use this formula:
=MINUTE(B2-B1)
If you want the difference in seconds in cells B2 and B1, use the following formula:
=SECOND(B2-B1)
While these functions are ideal for providing the total number of hours, minutes, or seconds, they are limited to those specific parts of the time. If you need more, such as both the hours and minutes, you can use the next option.
Use Subtraction and Manually Format the Result
Just like subtracting dates or numeric values in Excel using the minus sign, you can do the same with times. What’s different is that after you calculate the difference, you must manually format the result as hours, minutes, and seconds. Let’s take a look.
Here we want the total difference between cells B2 and B1 including hours and minutes. You would enter the following Excel formula:
=B2-B1
You’ll notice that the result is formatted as a time rather than the numbers of hours and minutes. This is a quick change.
- Select the cell with the result and do one of the following to open the formatting options:
- Right-click, pick Format Cells, and go to the Number tab.
- Select the Number drop-down box on the Home tab and choose More Number Formats.
- On the Number tab, choose the Custom format on the left.
- To the right, use the scroll box to pick the format “h:mm” and select OK.
You should then see your time difference in hours and minutes.
Using the same formula above to subtract the times and use different formatting, you can display the hours, minutes, and seconds or just the minutes and seconds. In the Format Cells dialog box pick “h:mm:ss” or “mm:ss” respectively.
This option is simple because you’re merely subtracting the time values; however, you do have the extra step of manually formatting the result.
Use the TEXT Function to Automatically Format the Result
One more way to calculate a difference in times in Excel is using the TEXT function. You’ll still subtract the cells containing the times but display the result as text in a specific format.
The syntax is TEXT(value, format) with the format argument placed in quotes. Here are a few examples using this option.
To subtract the times in cells B2 and B1and format the result as hours, you’d use this formula:
=TEXT(B2-B1,”h”)
To subtract the times in the same cells and format the results with hours and minutes, use this formula:
=TEXT(B2-B1,”h:mm”)
If you want to include seconds, simply add this after the minutes in the format argument as shown here:
=TEXT(B2-B1,”h:mm:ss”)
With the TEXT function you can not only obtain the difference between your times, but format it correctly at the same time. The only downside to this option is that the result cell is formatted as text making it more difficult to use in another calculation.
Take Your “Time”
Performing time calculations in Microsoft Excel isn’t as straightforward as you might think. However, using these three methods, you can subtract time to obtain working hours, break minutes, or similar times with a simple formula. Take the “time” to see which works best in your worksheet.
For more Excel tutorials, look at how to fix formulas that aren’t working correctly.
