Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

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

1 Answer

up vote 1 down vote accepted

Using OpenOffice.org 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:

HH:MM:SS

And for the Elapsed column, I used

[HH]:MM

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

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.