Solved: Quarter Hours?

[TrippyTom]

Hi everyone,

I would like to setup some conditional format to highlight if a TIME entered is on the quarter hour (i.e. 12:15pm, 12:30pm, 12:45pm, 1:00pm, etc.).

I was thinking I could use MOD but time is really just a decimal, so how would you do something like this?

[RichardSchollar]

Hi

You could use a conditional formatting formula of:

=MOD(A1*24,0.25)=0

Assuming the time was in A1.

Richard

[TrippyTom]

Perfect! :)

Thanks Richard.

[TrippyTom]

actually, it's not fully working for some reason. I'm getting odd results (not 0) on some of my rows. I tried to post a screenshot but when I try to paste the code from HTMLmaker it shows up as CODE, not the worksheet.

[edit] -- I changed the formula to highlight if it's NOT a quarter hour
=MOD($C2*24,0.25)<>0

[RichardSchollar]

Ok can you describe what these cells contain that are returning odd results? Are they hardcoded values or do they contain a formula? If so, what is the formula?

It would be useful to actually write the formula that I provided in a worksheet cell, so that you can examine all the calculation steps to determine why the unexpected result is being returned.

Richard

[TrippyTom]

Trying to attach an image of what i'm getting. In the image below, I would expect only rows 9, 10 and 12 to be highlighted.

[RichardSchollar]

What are the times being generated from? Are they exact times or is there maybe a second component that is obscured by formatting? You could attach a small sample of your workbook - I think that would make things a bit easier for me/others to debug.

Richard

[TrippyTom]

ok hope this attachment works...

[Bob Phillips]

Try this formula

=MOD(MINUTE(C2),15)=0

[TrippyTom]

Hey, it worked. :)
Thanks guys.