Calculate hours between two times

Calculate hours between two times

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:

  1. Open Excel
  2. Type the formula =IF(end > start, end – start, 1-start + end)
  3. Press Enter
  4. The formula returns the number of hours between the two times.

Example

In this example, we have a dataset in range B3:C8, which contains start and end times, with some end times occurring the following day. We want to calculate the elapsed time between the start and end times and display the results in column E.

We use an IF formula in cell E3 to calculate the number of hours between two times:

=IF(C3>B3,C3-B3,1-B3+C3)

Calculate hours between two times

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

Let’s evaluate the formula:

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.

  1. Condition logic with IF: IF(end > start, …) checks if the end is greater than the start.
  2. IF TRUE, when the end is greater than the start, the formula uses end—start to find the difference directly.
  3. 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.

MOD formula

Formula:

=MOD(end – start, 24)

The result is the same as the previous example.