Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

The formula failing is the following:

=IF(TIMEVALUE(C2 & ":00") > TIMEVALUE(B2 & ":00"); 0; C2-B2)

I previously tried

=IF(C2 > B2; 0; C2-B2)

but this also gives me "Error 502".

The cells it is referring to contains data in the format "12:30" (I formatted the columns with format "HH:MM").

I just want to calculate how much time lies between two times, respecting the special case where endtime < starttime.

share|improve this question
It would help if you can explain what you want this formula to do. – C. Ross Oct 7 '09 at 17:29
if the second cell has the later time it should return 0, otherwise is should return the time difference in hours and minutes. – cody Oct 7 '09 at 22:06
up vote 1 down vote accepted

Using 3.1.1 with the following data:

Start          End            Elapsed
12:30:00 PM    05:30:00 PM    05:00:00
08:30:00 AM    05:30:00 PM    09:00:00
08:30:00 AM    08:00:00 AM    00:00:00

I used the following formula for the Elapsed column:

=IF(B2 < A2; 0; B2-A2)

Start and End use the formatting:


And for the Elapsed column, I used


This seems to give the results you want.

share|improve this answer
But I cannot use AM/PM. The data is in the format I explained. – cody Oct 8 '09 at 11:47
You have to either use 24-hour time, or use AM/PM. Otherwise it will just be a guessing game as to what was meant. If you change the formats in example above, it still works. Where is there problem? – Matt Haley Oct 8 '09 at 14:57
thanks it works now it was the format code I used HH:MM for result instead of [HH]:MM – cody Oct 8 '09 at 22:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .