PDA

View Full Version : Solved: Quarter Hours?



TrippyTom
08-14-2007, 08:46 AM
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
08-14-2007, 08:59 AM
Hi

You could use a conditional formatting formula of:

=MOD(A1*24,0.25)=0

Assuming the time was in A1.

Richard

TrippyTom
08-14-2007, 09:03 AM
Perfect! :)

Thanks Richard.

TrippyTom
08-14-2007, 09:24 AM
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
08-14-2007, 09:34 AM
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
08-14-2007, 09:37 AM
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
08-14-2007, 09:48 AM
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
08-14-2007, 09:50 AM
ok hope this attachment works...

Bob Phillips
08-14-2007, 09:57 AM
Try this formula

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

TrippyTom
08-14-2007, 10:10 AM
Hey, it worked. :)
Thanks guys.