conditional formatting displays incorrect value when time is 12:00

**moonbecky** · 08-28-2008, 06:39 AM

hello,

I've heard from my collegues that this forum is one of the best since they were able to receive answers on their questions regarding excel vba and I would like to try my luck on this. I have a test file which has time in and time out and remarks. User enters time in and time out, then remarks automatically appears. Program works fine but not on one thing. I have an incorrect output when time entered is 12:00 - 12:30. Normally the output remarks on this should be "on time" but what happens is that the output appears "0:00:00" which should not. Do you have any idea why is this so? and besides it only happens when 12:00 - 12:30 is entered. Thanks in advance.

Sincerely,

Becky

**Richard Schollar** · 08-28-2008, 07:34 AM

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

**moonbecky** · 08-28-2008, 10:59 AM

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:

Please Login or Register  to view this content.

is when adding seconds to the value.

Please Login or Register  to view this content.

Thanks for helping me.

Sincerely,

Becky

**Richard Schollar** · 08-28-2008, 11:32 AM

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

**moonbecky** · 08-28-2008, 11:53 AM

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

**Richard Schollar** · 08-28-2008, 12:04 PM

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

**moonbecky** · 08-28-2008, 12:44 PM

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