How do I calculate the difference between two time values in Excel?

Question

Using Excel 2007, how do I calculate the time difference by entering

• a start time
• an end time

What format do I use in the cells where the time is?

For example, I'd like to be able to enter 0800 and see 0800 in the box, or enter 1430 and see 1430 in the box. Then I'd like to have the formula box display the time lapsed in hours and minutes.

• A1 = 0800
• A2 = 1430
• A3 (A1-A2) = 6.30*

Answer 1

Try this formula on A3:

=TEXT(A2,"00\:00")+(TEXT(A1,"00\:00")>TEXT(A2,"00\:00"))-TEXT(A1,"00\:00")

It should also account for durations that cross midnight (see example below). You can enter times in A1 or A2 without colons.

In the example below, A1 contains the Start time and A2 contains the End time.

Number Formats used:
A1: Custom > 0000
A2: Custom > 0000
A3: Custom > [h]:mm or h.mm (based on your example)

If you want to compute for hour fractions instead (i.e. 2.50 hours instead of 2:30), set A3's number format to General and then change the formula to:

=24*(TEXT(A2,"00\:00")+(TEXT(A1,"00\:00")>TEXT(A2,"00\:00"))-TEXT(A1,"00\:00"))

Answer 2

Just type them in normally, with colons, and use standard addition/subtraction:

Then, select the range, and under number format on the Home tab, click the dropdown and choose more number formats.

In the resulting dialog, set the format to hhmm (in other words, remove the colon from the default time format).

Now, your times will be formatted without the colon, and the subtraction will take care of calculating the time difference.

Unfortunately, you will still have to enter times with the colon, but they will display without it. If you enter the time without the colon, Excel thinks it's a date, and will break the setup.

Answer 3

You can do something like this, but it still requires the numbers to have been typed with colons (i.e. 8:00 instead of 800)