Date comparison problem

  • Thread starter Thread starter David
  • Start date Start date
D

David

We have a document set up which does conditional
formatting for cells that have 2 pound signs in them (##),
and it changes the background color.

We have a few columns, one with start time, one with end
time among others. There's also columns for every hour and
half-hour of the day.

When you select a start time and end time, the boxes
between the appropriate start and end times fill with ##
and shade.

Formula is something like: =IF($B3<K$2, IF($H3
J$2,"##",""),"")
Click to expand...

If our end time is 2:30, the box shares UP TO 2:30 PM.
This becomes a problem after the columns hit double-letter
(i.e. AA, AB, AC...) then the date comparison fails and
the box shades the 1/2 hour box. Essentially, it is saying
that once we have double-letter column names that
Time1>Time1 is True, even though time1 is equal.

Is this a glitch? This is with Excel XP (2002). I can
email the file if anyone needs to look at it.
 
If you format that 2:30 PM as general, you'll see it actually equals:
0.604166666666667

Maybe the value that you compare it to is very close (so it looks the same with
"hh:mm AM/PM" format), but really different.

Maybe you can add a very small number to look for differences:

=if($b3<K$2+.00000000001, .....
 
Back
Top