Conditional formatting time in minutes glitch?

[SunTrader]

For Excel 2007 -

I've got a worksheet with over a thousand filled cells with time 1:30 PM style (this might not matter I suppose?). It has a "start-time" of sorts of 6:00 PM and advances a minute at a time thereafter.

A check-cell, that I manually adjust, applies conditional formatting to the sheet based on this cell's time value. Everything works fine ... until it gets to 6:00 PM, 6:26 PM, 6:44 PM and I suppose many other minute times further down the timeline. I've tried changing the layout as well as of course using another worksheet but no luck. I've used both +Time(0,1,0) added directly in each cell or adding same but by way of referencing a master step cell. Always the same result.

I think I vaguely remember reading something a while back off a google search about this phenomenon but no workable solution??? Not sure?

Appreciate any assistance.

 

[SunTrader]

typo above

works fine ... until it gets to 6:10 PM, 6:26 PM, 6:44 PM

 

[SpillerBD]

? So what is the problem?
Conditional formatting is trying to evaluate what?

 

[SunTrader]

? So what is the problem?
Conditional formatting is trying to evaluate what?

When I change the time in the check-cell from the start-time 6:00 PM minute by minute it color highlights as it should 6:01, 6:02, 6:03 .... but then does nothing when it gets to 6:10. Works again at 6:11, 6:12 etc nothing at 6:26. Starts working again at 6:27 .... and stops again at 6:44. And so on. There doesn't seem to be any correlation between the nonworking cells so I am stumped.

 

[SpillerBD]

When I change the time in the check-cell from the start-time 6:00 PM minute by minute it color highlights as it should 6:01, 6:02, 6:03 .... but then does nothing when it gets to 6:10. Works again at 6:11, 6:12 etc nothing at 6:26. Starts working again at 6:27 .... and stops again at 6:44. And so on. There doesn't seem to be any correlation between the nonworking cells so I am stumped.

Why should 6:01, or of those cells, be highlighted?
I understand your trying to use "Check Cell" to feed your criteria, but can you copy that conditional formatting formula?

 

[shg]

The combination of accumulation of floating-point error and whatever conditional formatting formula you're using is probably the problem.

If you want to increment by minutes down a column, round:

=mround(a1 + "0:01", "0:01")

 

[SunTrader]

Why should 6:01, or of those cells, be highlighted?
I understand your trying to use "Check Cell" to feed your criteria, but can you copy that conditional formatting formula?

Just simple basic - cell value equal to {check-cell# in this case $Q$2}

 

[SunTrader]

The combination of accumulation of floating-point error and whatever conditional formatting formula you're using is probably the problem.

If you want to increment by minutes down a column, round:

=mround(a1 + "0:01", "0:01")

Ok that worked, strangely for 6:26 but not 6:10?

Tomorrow's another day - to add snippet further down the timeline to see if it is only 6:10 and go from there.

Thnx

 

[shg]

You've not said what your CF formula is or what you're trying to do, so ... good luck.

 

[SunTrader]

Thnx shg your reply last August .... =mround(a1 + "0:01", "0:01") ... was spot on and saved the day. Better late than never but thanks again.

Anyway of course now I got another one I am stuck on if you can kindly help as well.

I've got two columns of dates/times both defaulted to 3/14/01 1:30 PM format. First column starting from 6:00 PM has (minute) time values in clockwise order but with some duplicates as well as some gaps - as an example 6:00 PM, 6:01 PM, 6:01 PM, 6:04 PM, 6:06 PM etc. Second column starts from 6:00 PM but continues minute by minute uninterrupted till same time (6:00 PM) following day.

What I am trying to accomplish is to identify, along side second column lining up with each minute timeframe how many values from column one match it. So using the times I mentioned in first paragraph as an example:

6:00 PM 1
6:01 PM 2
6:02 PM 0
6:03 PM 0
6:04 PM 1
6:05 PM 0
6:06 PM 1

Hope I am clear.

Oh also BTW I used your prior advice ... =mround(a1 + "0:01", "0:01") ... to make sure these time values, which are grabbed from another worksheet are true exact minute values, but I am stumped how to accomplish what I need. I tried countif many different ways and other functions with no luck. Appreciate if you something else you can suggest.

Thank you.