Calculating the Time Difference Between Two Times in Excel4 Different Ways!
Determining the number of hours between two specific times can be useful for a variety of reasons.
This could include calculating the duration of a task or the travel time to a destination.
The calculation may also be necessary for other calculations, such as determining the hourly pay or hourly rent.
In this tutorial, we will explore three methods for calculating the time difference between two times in Excel:
- Using simple multiplication
- Using the TEXT function
- Using the HOUR function
Representation of Time in Excel
In Excel, time is expressed as a proportion of a 24-hour period. This translates to time that is internally stored as a decimal, with 24 hours equating to a value of 1.0.
An hour is equivalent to 1/24 of a day. Thus, it is represented as 0.0416667 or 1/24 in Excel.
The time value of 12:00:00 is represented as 0.5, as it is half of a 24-hour day. Similarly, the time value for 06:00:00 is 0.25 as it is a quarter of a day.
Furthermore, a minute is represented as 1/60 of an hour and a second is represented as 1/3600 of an hour.
Determining the Time Difference between Two Times in Excel
Since time is represented as a decimal number in Excel, determining the time difference between two times is simply a matter of finding the difference between their respective decimal values, as shown below:
|Time difference = End time – Start time|
Let’s say we have a given time with start and end as shown below:
Calculating the time difference between the two times should be straightforward.
The process involves simply determining the difference between the decimal values of the two times:
However, the above formula yields the time difference in hours, minutes, and seconds.
To obtain only the number of hours, it is necessary to extract it from this result.
There are various methods for extracting the hours from this value.
In this guide, we will examine three such methods:
- By simple multiplication
- Using the TEXT function
- Using the HOUR function
Let us examine each of these methods and how they can assist in determining the time difference between two times in Excel.
For the purpose of demonstration, we will utilize the following set of start and end times for each method:
Determining the Time Difference between Two Times in Excel using Simple Multiplication
To calculate the number of hours, the traditional method is to multiply the time difference by 24 (as a day is 24 hours) and then round the result to the nearest integer (if a fractional value is obtained).
This method can be applied to our sample data to calculate the number of hours between each start and end time. Using the data from the first row, the formula would be:
The formula above calculates the difference between the end time and start time in the form of a time value (2:05:00), which corresponds to a serial number of 0.086806.
It then multiplies this serial number by 24 to determine the number of hours it represents. This results in a fractional value (2.08333), as it takes into account hours, minutes, and seconds.
To obtain only the number of hours, we must use the INT function on the result, which rounds the value to the nearest integer.
The final outcome when applying the formula to all the rows in the given dataset is shown below as:
It’s important to note that the formula returns a negative value when the end time is earlier than the start time.
Determining the Time Difference between Two Times in Excel using the TEXT Function
The function is called “TEXT” as it returns the number in the form of a text value. The syntax for the TEXT function is straightforward:
In this formula, “value” is the number you wish to format, and “format_code” is a string that specifies the desired format for the value.
When the first parameter is a TIME value, you can use a combination of codes to specify the appearance of the result:
- h – for displaying hours as 0-59
- hh – for displaying hours as 00-59
- m – for displaying minutes as 0-59
- mm – for displaying minutes as 00-59
- s – for displaying seconds as 0-59
- ss – for displaying seconds as 00-59
- AM/PM – for displaying the time using the 12-hour clock
If AM/PM is not included in the format code, the time displayed will be based on the 24-hour clock.
Here is an example of how the TEXT function can be used to determine the time difference between each set of start and end times in our sample data.
In the first row, use this formula:
The formula above calculates the difference between the end time and start time in the form of a time value (2:05:00). It then formats the output to extract and display only the hours from the obtained time.
Here is the result obtained when the formula is applied to all the rows in the given worksheet:
As an alternative, you can also find the difference between B2 and A2, and then use the “h” cell format through the Format Cells dialog box as shown below:
- Select the cells you wish to format
- Press CTRL+1 or right-click on the selected cells, then select Format Cells from the context menu.
- In the Number tab, choose Custom from the Category list.
- In the Type field, enter the format code “h”.
- Click OK to close the Format Cells dialog box.
A significant drawback of using the TEXT function to determine the time difference between two times is that it returns a #VALUE! error if the end time is earlier than the start time.
Additionally, the value returned is in the form of text, so it cannot be used for further calculations without first converting it to a numeric value.
Determining the Time Difference between Two Times in Excel using the HOUR Function
The simplest method for extracting the number of hours from a time value is by using the HOUR function. It returns the hour value of the specified time (as a number between 0-23).
For example, if the time 5:30 is passed as a parameter, it will return the value 5.
This function can be applied to our sample data to calculate the number of hours between each start and end time.
Using the data from the first row, the formula would be:
The formula above computes the difference between the end time and start time in the form of a time value (2:05:00).
The HOUR function then extracts and returns the hour component of the time difference gained.
The final outcome when the formula is applied to all the rows of the given worksheet:
It’s important to note that similar to the TEXT function, the HOUR function also returns a #NUM! error when the end time is earlier than the start time.
In this article, we presented three different formulas that can be used to determine the time difference between two times in Excel.
While the second and third formulas are straightforward and easy to apply, the first formula (using basic arithmetic) can handle both negative and positive differences effectively.
It is recommended to select the method that is most suitable for your specific task.