Calculate hours between two times

In this example, the goal is to calculate the number of hours between two times. This basic problem comes up frequently when tracking time and may be described in various ways:

• Calculate working time in hours.
• Calculate the duration of an activity in hours.
• Calculate the hours needed for a task.

Calculating the hours between two times is oddly complicated. In part, this is because Excel stores time as fractional values. For example, 0.25 is 6:00 AM or 6:00 hours, depending on formatting. The number 0.25 makes sense when you consider that 6 hours is one-quarter of a day, and a day in Excel equals 1. But it isn't the way most people think about time.

The other reason time is complicated is that it resets to zero again at midnight. If you have a start time and an end time that occur on the same day, you can subtract the start time from the end time and end up with a positive number that represents the hours between the two times. However, if the times cross midnight, the start time can be greater than the end time, and you'll end up with a negative time, which Excel does not support.

When solving this problem in Excel, there are three important factors to consider: (1) Do the times cross midnight? (2) Do time durations exceed 24 hours? (3) Are the times part of a date? The article below describes three formulas for calculating the number of hours between two times. It also explains how to format time and calculate the hours between times as a decimal value.

Table of Contents

• Formula options
• How Excel tracks time
• Formula 1: Simple duration calculation
• Formula 2: When times cross midnight
• Formula 3: Clever MOD alternative
• Dates with times
• Number formatting for time
• Calculating decimal hours between times

Formula options

Below are the three formulas explained in this article. The first formula works fine if the times occur on the same day and do not cross midnight. It also works well for dates that contain time, which is explained here. The second formula is a traditional formula based on the IF function to handle times on the same day and times that cross midnight. The third formula is an elegant alternative to the second formula.

=end-start // for times on same day or dates with times
=IF(end>start,end-start,1-start+end) // for times that cross midnight
=MOD(end-start,1) // clever alternative

To start, let's review how Excel handles time.

How Excel tracks time

In Excel, dates are serial numbers, and one day has a numeric value of 1. Time is a fractional value of 1, and 1 hour = 1/24 = 0.041666667. This means that 6 hours is one-quarter of a day and has a numeric value of 0.25, 12 hours is half a day and has a value of 0.5, 18 hours is three-quarters of a day and is 0.75, and 24 hours is 1 day with time reset to zero. In the same way, 6:00 AM has a numeric value of 0.25, 12:00 PM has a value of 0.5, 6:00 PM has a value of 0.75, and so on, as summarized in the table below:

Back in Excel, the screen below shows the times in the worksheet formatted as regular numbers:

You can see that 6:00 AM is 0.25, 6:00 PM is 0.75, 12:00 PM is 0.5, midnight is zero, and so on. Because an hour in Excel is 1/24, you can multiply time by 24 to get a decimal hour. For example 0.5 * 24 = 12 hours, 0.25 * 24 = 6 hours, etc. See below for more details.

Formula 1: Simple duration calculation

When start and end times occur on the same day, calculating duration in hours is straightforward because the end time will be a larger number than the start time. In that case, you can use a simple formula like this:

=end-start

For example, with a start time of 6:00 AM and an end time of 12:00 PM:

="12:00 PM"-"6:00 AM"
=0.5-0.25
=0.25

When formatted with the number format "h:mm", Excel will display 6:00. This formula works well for times that occur on the same day. However, if times cross midnight, it will fail.

Formula 2: When times cross midnight

Excel handles time as a 24-hour clock. As a day progresses, the time value increases, approaching 1 toward midnight. However, at midnight, the 24 hours will add up to 1 day, and the time value will reset to zero. If you type a zero into a cell and apply time formatting, Excel will display the time as midnight. This makes sense as a clock, but it makes calculating time more difficult when times cross midnight. For example, if the start time is 9:00 PM one day, and the end time is 6:00 AM the next day, the end time is less than the start time, and the formula above will return a negative value:

="6:00 AM"-"9:00 PM"
=0.25-0.875
=-0.625

Excel does not support negative time or date values and will display a string of hash characters (########) when time formatting is applied to a negative number. You can see what this looks like in the screen below:

To correct this problem, you can use a formula like this:

=IF(end>start,end-start,1-start+end)

With cell references added, the formula in cell E5 becomes:

=IF(C5>B5,C5-B5,1-B5+C5)

This formula is designed to handle both cases: times that occur on the same day and times that cross midnight. The IF function checks the times and applies the correct formula. To start, we test the end time against the start time:

=IF(end>start,

If the end time is greater than the start time, the times belong to the same day, and we run the simple formula above:

=end-start

If, however, the end time is not greater than the start time, we assume the times cross midnight, and in that case, we run:

=1-start+end

By subtracting the start time from 1, we get the amount of time on the first day, which we add to the time on the second day, equal to the end time. This works because 24 hours is 1 day, and the point at which time resets to zero. When we subtract the start time from 1, we get the time until midnight. When we add the end time, we add the time from midnight until the end time. The two times together are the total elapsed time. You can see the result in the worksheet below, where the formula in E5 is:

=IF(C5>B5,C5-B5,1-B5+C5)

To recap, if the end time is greater than the start time, the times are on the same day, and the simple formula is used. Otherwise, the times cross midnight, and the second formula is used.

Formula 3: Clever MOD alternative

One complication of the formula above is that we need to calculate elapsed time with two different formulas. The MOD function provides an elegant way to simplify the problem and apply a single formula that will work for both scenarios:

=MOD(end-start,1)

Because this formula will handle times that cross midnight, we don't need a conditional IF statement. The way the formula works, however, is more cryptic because it depends on modular arithmetic, a system of arithmetic for integers, where numbers "wrap around" when reaching a certain value, called the modulus.

The MOD function returns the remainder after division. The result has the same sign as the divisor. Inside MOD, we supply the number by subtracting the start time from the end time. For the divisor, we provide the number 1. When you divide any number by 1, the result is the number itself. This is because division by 1 does not change the value of the number. However, when MOD calculates a remainder, it handles positive numbers differently from negative numbers. We can use this difference to our advantage with time in Excel.

When times occur on the same day, the end time will be greater than the start time, and the number will be positive and a valid Excel time. For example, with a start time of 9:00 AM and an end time of 3:00 PM, the number after subtraction will be 0.25 (6 hours), which MOD will return unchanged:

=MOD(0.625-0.375,1)
=MOD(0.25,1)
=0.25 // 6 hours

However, when times cross midnight, the end time will be less than the start time, and the number will be negative. For example, with a start time of 9:00 PM and an end time of 3:00 AM, the number after subtraction will be -0.75, which is not a valid Excel time. The MOD function takes care of this problem by "flipping" the negative value to the correct positive value 0.25:

=MOD(0.125-0.875,1)
=MOD(-0.75,1)
=0.25 // 6 hours

In both examples above, the result is 0.25, or 6 hours, when formatted as an Excel time. To summarize, MOD will return the positive times unchanged and "flip" the negative times to a correct positive time.

=MOD(0.25,1) // returns 0.25
=MOD(-0.75,1) // returns 0.25

You can see the MOD formula applied in the worksheet below, where cell E5 contains:

=MOD(C5-B5,1)

The MOD function works very well here because a "modulus" has clock-like properties. For a good introduction to modular arithmetic, see this link on Khan Academy.

Note: The formulas above will not handle durations greater than 24 hours. If this is a requirement, see the "Dates with times" section below.

Date with times

Although it's not obvious, Excel can work with dates that include a time value, sometimes called a "datetime." This can greatly simplify calculating elapsed time because we no longer need to worry about times crossing midnight, and we can calculate elapsed times over 24 hours. When a date in Excel has a time component, the date will appear as a whole number, and the time will appear as a decimal value. For example:

1. The number 45457 is equivalent to "June 14, 2024" in Excel.
2. The number 45457.5 is equivalent to "June 14, 2024, at 12:00 PM" in Excel.

The whole number to the right of the decimal (45467) is the date (June 24, 2024), and the decimal value (0.5) is the time (12:00 PM). To enter a date with a time, place a single space between time and date like this: "24-Jun-2024 12:00". If you format this date with General format (keyboard shortcut Control + ~), you'll see a value like this:

45467.5 // date + time

When calculating the hours between two times, dates with times are ideal because they automatically handle times that cross midnight. Unlike simple time values, there is no danger that the start time will be greater than the end time because, by definition, later dates with times must be larger numbers. As a result, when working with dates that include time, we can revert to the simple formula:

=end-start

You can see this formula below where start and end values contain both dates and times, and the formula in D5 is:

=C5-B5 // end-start

The result is formatted with a custom number format to display elapsed hours:

[h]:mm

This format will correctly display the hours between two times when the difference is over 24 hours, as seen in the workbook below:

Number formatting for time

What causes Excel to display 0.5 as "12:00 PM" is number formatting. Below are two common number formats for time. The first will display 0.75 as 6:00 PM, and the second will display 0.75 as 18:00:

h:mm AM/PM // display like 6:00 PM
h:mm // display like 18:00

By default, Excel may display time with AM/PM. For example, if you have a calculated time of 6 hours, Excel may display this as 6:00 AM. To remove the AM/PM, apply a custom number format like this:

h:mm

As mentioned above, when elapsed time may exceed 24 hours, add square brackets around the h:

[h]:mm

The square bracket syntax [h] tells Excel to display hour durations greater than 24 hours (e.g., display 36 hours as 36:00). If you don't use the brackets, Excel will reset time to zero when the duration hits 24 hours (like a clock).

Calculating decimal hours between times

The formulas above all return native Excel time as a result. This is great when you want to display hours and minutes, but it is inconvenient when you want to, for example, multiply calculated hours by an hourly rate. For such calculations, you will want to convert the result to decimal hours. To do this, just multiply the Excel time by 24. You can see an example below, where we calculate the hours between two times with the MOD formula above and multiply the result by 24. The formula in cell E5 is:

=MOD(C5-B5,1)*24

When you convert to decimal hours, change the number format to suit. The number format in the worksheet above is "0:00" to display a number with two decimal places. You can use "0.0" to display a single decimal place.

Recommendations

• Using dates with times is the best option because it eliminates the problem of times crossing midnight. As a result, you can use Formula 1, and it will always work. In addition, you can work with time durations over 24 hours.
• You can also use Formula 1 if times always occur on the same day and don't ever cross midnight.
• If times do cross midnight, use Formula 2 or Formula 3, depending on your preference. Both formulas will return equivalent results. 
• Pay attention to the number formatting applied to times. Periodically apply the General format to time values to check your understanding of the numbers underneath.
• Convert Excel to decimal hours as needed in order to perform calculations like hours * pay.