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.
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 alternativeTo 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:
| Hours | Time | Fraction | Value |
|---|---|---|---|
| 3 | 3:00 AM | 3/24 | 0.125 |
| 6 | 6:00 AM | 6/24 | 0.25 |
| 4 | 4:00 AM | 4/24 | 0.167 |
| 8 | 8:00 AM | 8/24 | 0.333 |
| 12 | 12:00 PM | 12/24 | 0.5 |
| 18 | 6:00 PM | 18/24 | 0.75 |
| 21 | 9:00 PM | 21/24 | 0.875 |
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.
Video: How to use time formatting in Excel
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-startFor 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.25When 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.625Excel 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-startIf, 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+endBy 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)When times cross midnight, the problem becomes tricky since the end time may be less than the start time. The MOD function takes care of the negative problem by "flipping" negative values to the required positive value.For example, to calculate hours between 9 PM and 3 AM, MOD works like this:
=MOD(0.125-0.875,1)=MOD(-0.75,1)=0.25 // 6 hoursThe MOD function returns the remainder after division. Inside MOD, we supply thenumber by subtracting the start time from the end time.For the divisor, we provide the number 1.Because this formula will handle times on the same day and times that span midnight, we don't need a conditional IF statement. You can see the result in the worksheet below, where the formula in cell E5 is:
=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:
- The number 45457is equivalent to "June 14, 2024" in Excel.
- 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 + timeWhen 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-startYou 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]:mmThis format will correctly display the hours between two times when the difference is over 24 hours.
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 PMh:mm // display like 18:00By 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:mmAs mentioned above, when elapsed time may exceed 24 hours, add square brackets around the h:
[h]:mmThe 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, youwill 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 resultby 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.
