How to Calculate the Difference Between Two Times in Microsoft Excel

Plus what to do before you begin

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 inExcel 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 selectTimein theNumberdrop-down box on theHometab or use theFormat Cellsdialog 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 likesubtracting datesor numericvalues in Excelusing 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.

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 theformatargument 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 theformatargument 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 resultcell is formatted as textmaking 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 athow to fix formulasthat aren’t working correctly.

Sandy Writtenhouse is a freelance technology writer and former Project, Department, and Program Manager. She turned her education, job experience, and love of technology into a full-time writing career. With all sorts of gadgets in her home and her hands, she seeks to help others make life easier through technology.Read Sandy’s Full Bio

Your email address will not be published.Required fields are marked*

Comment*

Name*

Email*

Website

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

How to Calculate the Difference Between Two Times in Microsoft Excel#

Before You Begin, Format the Times#

Use the Time Functions for Basic Results#

Use Subtraction and Manually Format the Result#

Use the TEXT Function to Automatically Format the Result#

Take Your “Time”#

Leave a Reply#