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

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*`
-

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"))
``````
-
Thank you thank you thank you! This works GREAT! – selahb8 Aug 18 '11 at 1:37

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.

-
Thanks for your feedback! – selahb8 Aug 18 '11 at 1:43

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)

-
The OP wants to have the time displayed as `0800` though. – slhck Aug 17 '11 at 21:04
Use Type hhmm in the Custom section – variant Aug 17 '11 at 21:06
Thank you for your feedback! – selahb8 Aug 18 '11 at 1:43

=minute(C1-C2) will give you minutes of difference between the two time in cells C1 and C2. Make sure the cells C1 and c2 have the format of "Time"

-