Excel – How to calculate the difference between two time values in Excel

date timemicrosoft-excel-2007worksheet-function

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*

Best Answer

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.

enter image description here

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"))
Related Question