AND() function in conditional formatting

[demonfly100]

Hi all,

Trying to set up conditional formatting on a spreadsheet to fill cells based on the day of the week it is and a certain required time on that day.

E.g. On Saturdays, the time entered must be before 7:55 to fill green.

Any thoughts on this? any help would be appreciated

Thanks

[Ace_XL]

In Conditional formatting use..

=AND(WEEKDAY($A$1)=7,TIME($A$1)<=TIME(7,55,0)) -- fill green

[Richard Buttrey]

Hi

=AND(WEEKDAY(A1)=7,TIME(A1)<=TIME(7,55,0))

[demonfly100]

Hi,

Thanks very much for taking the time to reply. I don't think I explained my intentions particularly well, but I've created a template spreadsheet showing what I need the conditional formatting to do. Thanks again for your time

excel forum template.xlsx

[Richard Buttrey]

Hi,

In which case no AND is needed if the successive days are in order, in B2 copied down

=OFFSET(B2,7-ROW(),0)<=TIME(7,55,0)

Note adjust the 7-ROW() bit if your data is in a row starting other than row 2

[Ace_XL]

In that case..

=OR(AND(WEEKDAY(A2)<>7,B2<=TIME(7,25,0)),AND(WEEKDAY(A2)=7,B2<=TIME(7,55,0))) ---- fill green
=OR(AND(WEEKDAY(A2)<>7,B2>TIME(7,25,0)),AND(WEEKDAY(A2)=7,B2>TIME(7,55,0))) --- fill red

[demonfly100]

Hi again,

Sorry, I think I'm getting a little confused now; I selected the row with the times in it and entered the formula in the conditional formatting box, but nothing seems to have changed, even after I have changed the times in some of the cells. Also, Is it possible to conditionally format based on whether the adjacent cell in the date column to the left is a saturday/week day? I ask purely because if somebody forgets to fill out one of the days, would it not throw the rest of the formula?

Thanks again for your time and patience.

[demonfly100]

Hi Ace_XL,

Thanks for the reply! The formulas are partly working; there are a few issues where weekdays with times before 7:25 are being highlighted as red, and others past 7:25 are highlighted as green. Also, in the formula is (A2) a cell reference?

Thanks

[Ace_XL]

there are a few issues where weekdays with times before 7:25 are being highlighted as red, and others past 7:25 are highlighted as green

Not sure why. Can you post a sample?

Also, in the formula is (A2) a cell reference?

yes, it is a relative reference and changes based on row number, although the formula will always show A2

[daddylonglegs]

I'd use this condition for green

=B2<=IF(WEEKDAY(A2)=7,"7:55","7:25")+0

[demonfly100]

Hi Daddylonglegs,

Thanks for the reply; would you be able to break the formula down and explain the different parts for me? If I can understand it I might be able to sort a similar problem out on my own next time instead of taking up everyone's time on here.

Thanks

[demonfly100]

Ace_XL,

I've got a screenshot of the problem here;

excel forum screenshot.jpg