Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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*
share|improve this question

4 Answers 4

up vote 2 down vote accepted

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"))
share|improve this answer
    
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.

share|improve this answer
    
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)

enter image description here

share|improve this answer
    
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"

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.