How to Calculate Hours between two Times in Excel?

by

Calculating the number of hours between two given times might be required for a number of situations.

You might need to calculate the number of hours needed to complete a task or to reach a destination.

It might also be a preliminary requirement to other computations, like computing hourly payroll, or hourly rent.

In this tutorial we will look at three different ways to calculate hours between two times in Excel:

  • Using simple multiplication
  • Using the TEXT function
  • Using the HOUR function

How Time is Represented in Excel

In Excel, time is represented as a fraction of a 24-hour day.

This means that time is internally stored as a decimal number, where 24 hours are represented with a value of 1.0.

An hour is simply one-twenty-fourth of a day. Therefore, an hour is represented as 1/24 or 0.0416667.

This means the time value for 12:00:00 in Excel is 0.5 (since 12 hours is half a day). Similarly, the time value for 06:00:00 is 0.25 because 6 AM is a quarter of a day.

In the same way, a minute is represented as 1/60th of an hour, and a second is represented as 1/3600th of an hour.

Also read: How to Add Hours to Time in Excel?

How to Calculate Hours between two Times in Excel

Since time is basically just stored as a decimal number in Excel, calculating the time difference just requires finding the difference between the two-time values, as follows:

Time difference = End time – Start time

So, for example, say you have the following start and end times:

Start and End time

Finding the time difference between these two times should be easy right? All you would need to do is find 

=B2-A2
Formula to calculate difference in two time values

However, this formula provides the entire time in hours, minutes, and seconds.

But if you want only the number of hours, you will need to extract it from this result.

There are a number of ways in which you can extract the number of hours from this value.

In this tutorial, we are going to discuss three such ways:

  • By simple multiplication
  • Using the TEXT function
  • Using the HOUR function

Let us look at each of these ways and how they can help calculate the number of hours between two times in Excel.

For each method, we are going to use the following set of start and end times:

Start and End time dataset

Calculating Hours between Two Times in Excel by Simple Multiplication

The traditional way of calculating the number of hours simply involves multiplying the time difference obtained with 24 (since a day comprises 24 hours) and then rounding the result to an integer value (in case we get a result in fractions).

Here’s how this can be applied to our data samples to calculate the number of hours between each start and end time.

For the first row, use the formula:

=INT((B2-A2)*24)

The above formula first calculates and gives us the difference between the end time and start time in the form of a time value (2:05:00).

The serial number corresponding to this time value is actually 0.086806.

The formula then multiplies this value by 24 to obtain the number of hours that the serial number corresponds to.

This then gives a fractional result (2.08333) since it considers the time in hours, minutes as well as seconds.

To extract just the number of hours, we need to apply the INT function to this result so that it rounds the value to the nearest integer.

In the end, here’s the result you get when you apply the formula to all the rows of the given dataset:

INT formula to get hours between time values

Notice the formula returns a negative value when the end time is less than the start time.

Also read: How to Separate Date and Time in Excel (7 Easy Ways)

Using the TEXT Function to Calculate Hours between two Times in Excel

This method involves the use of the TEXT function in Excel.

This function lets you change the appearance or format of a number, date or time by applying a given format code to it.

It’s called a ‘TEXT’ function because it returns the number formatted into a text value.

Syntax of the TEXT function is quite simple:

=TEXT(value, format_code)

Here, value is the number that you want to format and format_code is a string that specifies the format that you want to apply to the value.

If the first parameter is a TIME value, then you can use a combination of the following codes in order to specify how the result should look:

  • h – to display hours as 0-59
  • hh – to display hours as 00-59
  • m – to display minutes as 0-59
  • mm – to display minutes as 00-59
  • s – to display seconds as 0-59
  • ss – to display seconds as 00-59
  • AM / PM – to display the time based on the 12-hour clock

Note: If AM / PM is not specified, the time displayed is based on the 24-hour clock.

Here’s how the TEXT function can be applied to our data samples to calculate the number of hours between each pair of start and end times.

For the first row, use the formula:

=TEXT(B2-A2,"h")

The above formula first computes the difference between the end time and start time in the form of a time value (2:05:00).

Then, it formats the output to extract and display only the hours from the time obtained.

Here’s the result we get when we apply the above formula to all the rows of the given worksheet:

TEXT formula to get the hour between two time values

Alternatively, you could simply find B2-A2 and then apply the “h” cell format using the Format Cells dialog box, as follows:

  1. Select the cells for which you want to change the format
  2. Press CTRL+1 from your keyboard (or right-click on the selected cells and then click on the Format Cells option in the context menu that appears).
Click on Format Cells
  1. In the ‘Number’ tab, select ‘Custom ‘under the ‘Category’ list.
Select Custom format option in Format Cells dialog box
  1. In the input field under ‘Type’, enter the format code, “h”.
Enter h as the cell format
  1. Press OK to close the Format Cells dialog box.

One major drawback of using the TEXT function to calculate the number of hours between two times is that it returns a #VALUE! error if the end time is less than the start time.

Another drawback is that the value returned is a text value, so you cannot directly use it to perform subsequent calculations.

Also read: Convert Decimal to Fraction in Excel

Using the HOUR Function to Calculate Hours between two Times in Excel

Finally, let’s look at the easiest way to extract the number of hours from a time value.

The HOUR function returns the hour value of the specified time (as a number between 0-23). So if you pass the time 5:30 as a parameter to this function, it will return the value 5.

Here’s how this function can be applied to our data samples to calculate the number of hours between each pair of start and end times.

For the first row, use the formula:

=HOUR(B1-A1)

The above formula first calculates the difference between the end time and start time in the form of a time value (2:05:00).

The HOUR function then simply extracts and returns the hour component of the time difference obtained.

Here’s the result you get when you apply the formula to all the rows of the given worksheet:

HOUR formula to get hours between two times

Notice that similar to the TEXT function, the HOUR function also returns a #NUM! Error when the end time is less than the start time.

In this tutorial, we showed you three different formulas that you can use to calculate the number of hours between two times in Excel.

While the second and third formulas are simple and easy to use, the first formula (which uses simple multiplication) handles both negative and positive difference values well.

We encourage you to select the method that works best for you and the problem at hand.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment