conditional formatting displays incorrect value when time is 12:00

Hi Becky

This results from the way computers store floating point numbers. If you want to investigate this further a good place to start is here:

http://www.cpearson.com/excel/rounding.htm

(Times in Excel are floating point numbers).

Anyway, to correct your formula to take account of this, you need some way to round the B3-A3 calculation in C3. The easiest way to do this is to use the MINUTE function:

formula in C2 copied down:

=IF(COUNT(A2,B2)=2,IF(MINUTE(B2-A2)<=30,"ON TIME",B2-A2-"0:30"),"")

Richard

Hi Richard,

Thanks for the response and I think trying my luck here is indeed a success. first, the formula you have provided works perfectly and also thank you very much for giving me info regarding floating values. Well one thing I have in mind now is if the formula you have provided also works if I happened to have a seconds value? Like 12:00:00 - 12:30:01 will display 00:00:01. I was thinking if the formula you have provided which is:

=IF(COUNT(A2,B2)=2,IF(MINUTE(B2-A2)<=30,"ON TIME",B2-A2-"0:30"),"")

is when adding seconds to the value.

=IF(COUNT(A2,B2)=2,IF(SECOND(B2-A2)<=30,"ON TIME",B2-A2-"0:30:00"),"")

Thanks for helping me.

Sincerely,

Becky

Sorry, the first formula I provided was very poor and shouldn't be used. A better one which should take account of your seconds is:

=IF(COUNT(A2,B2)=2,IF(FLOOR(B2-A2,1/86400)<="00:30"+0,"ON TIME",B2-A2-"0:30"),"")

Richard

Hi Richard,

Thanks again. Will that formula also works if in case I happened to display the values without seconds? Like 12:00:00 to 12:31:00 will display 0:01. I just want to know if formula works on both ways. Because I saw that there was an added function or command named FLOOR and 1/86400 which I am not familiar what this value corresponds.

Sincerely,

Becky

I was using Floor to round the value down to the nearest second. Sorry about the 1/86400. This comes from there being 86400 seconds in a day (24*60*60) - I could just as easily have used:

=FLOOR(A2-B2,"00:00:01"+0)

It will work whether the seconds are displayed or not, so it should be suitable for what you want to do.

Richard

Hi Richard,

Thank you very much! I appreciate you taking time in helping me. This forum really rocks! No wonder my colleagues referred me to this forum. You guys are great! Thanks again.

Sincerely,

Becky