up vote 0 down vote favorite
share [g+] share [fb]

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.

link|improve this question
50% accept rate
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
feedback

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.

link|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
feedback

Your Answer

 
or
required, but never shown
discard

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