Learn how to calculate hours between two times in Excel using an IF formula with conditional logic to manage different scenarios.
How to calculate hours between two times?
Here are the steps to calculate the hours between start time and end time:
- Open Excel
- Type the formula =IF(end > start, end – start, 1-start + end)
- Press Enter
- The formula returns the number of hours between the two times.
Example
In the example, we have start and end times in column B and column C. We use an IF formula to calculate the number of hours between two times.
Formula in cell E3:
=IF(C3>B3,C3-B3,1-B3+C3)
If start and end are times on a clock (say start = 23 and end = 2), the formula interprets that the end is technically after the start in a 24-hour cycle, calculating a result of 3 hours forward by using 1 – start + end.
Explanation:
The formula =IF(end > start, end – start, 1 – start + end) calculates the difference between two values, end and start, handling cases where the end is either greater than or less than the start.
- Condition logic with IF: IF(end > start, …) checks if the end is greater than the start.
- If True, when the end is greater than the start, the formula uses end—start to find the difference directly.
- If False: When the end is not greater than the start (i.e., the end is less than or equal to the start), the formula calculates the difference by 1 – start + end. This approach can be useful in cases where start and end might represent cyclical values, like times on a 24-hour clock, ensuring that the result is positive or fits the intended cycle.
MOD function
You can use the MOD function to find the differences between two times.
Subtraction: end-start finds the direct difference between end and start.
The MOD function returns the remainder when dividing (end – start) by 24. This step ensures that if the difference is negative (for cases where the end is technically “earlier” than the start within the 24-hour format), the result wraps around to produce a positive outcome within the 0–23 range.
Formula:
=MOD(end – start, 24)
The result is the same as the previous example.