# How to Calculate Hours between two Times in Excel? - Spreadsheet Planet (2023)

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 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:

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

`=B2-A2`

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:

### 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:

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:

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).
1. In the ‘Number’ tab, select ‘Custom ‘under the ‘Category’ list.
1. In the input field under ‘Type’, enter the format code, “h”.
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.

### 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:

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 (that 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:

• Convert Military Time to Standard Time in Excel (Formulas)
• How to Convert Days to Years in Excel (Simple Formulas)
• How to Convert Date to Day of Week in Excel
• How to Add Days to a Date in Excel
• BETWEEN Formula in Excel (Using IF Function) – Examples
• How To Combine Date and Time in Excel
• How to Round Time to the Nearest Quarter Hour in Excel?
• How to Add Minutes to Time in Excel?

## FAQs

### How do I calculate hours worked between two times in Excel? ›

Calculate hours between two times: =TEXT(B2-A2, "h") Return hours and minutes between 2 times: =TEXT(B2-A2, "h:mm") Return hours, minutes and seconds between 2 times: =TEXT(B2-A2, "h:mm:ss")

How do you calculate the number of hours between two times? ›

To calculate the number of hours between two times:
1. Convert both times to a 24-hour time format.
2. Subtract the starting time from the ending time.
3. Do you have any seconds or minutes after the subtraction? If so: ...
4. Voila! You have calculated the hours between times.
Feb 2, 2023

What is the formula for calculating hours worked? ›

Just multiply the hours worked per day by the number of days worked per week. If an employee works different schedules every day, then you will need to calculate hours worked for each day. You will then need to add up the total for each day to get their hours worked in a given week.

How do you calculate time duration? ›

Calculate the duration between two times

First, identify the starting and an ending time. The goal is to subtract the starting time from the ending time under the correct conditions. If the times are not already in 24-hour time, convert them to 24-hour time. AM hours are the same in both 12-hour and 24-hour time.

How do I calculate time period in Excel? ›

Use the DATEDIF function when you want to calculate the difference between two dates. First put a start date in a cell, and an end date in another.
...
Calculate age in accumulated years, months, and days
1. Use DATEDIF to find the total years. ...
2. Use DATEDIF again with “ym” to find months. ...
3. Use a different formula to find days.

Top Articles
Latest Posts
Article information

Author: Frankie Dare

Last Updated: 01/13/2024

Views: 6511

Rating: 4.2 / 5 (73 voted)

Author information

Name: Frankie Dare

Birthday: 2000-01-27