Conditional Formatting - Everyones Bane

[KyleG]

I thought what i wanted would be simple but no matter how i do it i cant seem to get it to work.

I have a column (B) of times in the format [h:mm]. I want to hightlight non-normal hours.

Normal hours are 7:00 - 8:00 Monday to Friday. (Days listed in column A)
eg:
10:00 Monday - cell formatted.
No value Monday - cell formatted
No value Saturday - cell stays as normal
Any hours Saturday - cell formatted

thanks

 

[Bjornar]

Try this for your conditional formatting formula:

=OR(B1<TIMEVALUE("7:00"),B1>TIMEVALUE("8:00"),A1="Saturday",A1="Sunday")

With Weekday in A1, and your time in B1.

------
Forum posting problem:
Had to wrap my formula with PHP Code tags to get it to display properly, the ordinary CODE tags didn't do the trick. It removed everything between < and > in the formula.....

 

[Bjornar]

I see that I missed the part about blank time cells on weekends should stay unformatted. Try this formula instead, to take care of this:

=IF(OR(A1="Saturday",A1="Sunday"),B1,OR(B1<TIMEVALUE("7:00"),B1>TIMEVALUE("8:00")))

 

[KyleG]

Awesome thanks.

Made an alteration because i didnt give you the full details, re the date in the A column is a day, its a full date. Also the first 3 rows contained merged cells which appear to be causing hickups. These work perfect for the B and C row respectively


=IF(OR(WEEKDAY(A4)=1,WEEKDAY(A4)=7),B4, OR(B4<TIMEVALUE("7:00"),B4>TIMEVALUE("8:00")))
Edit:The above line is not posting correctly it contains the part of the original code you provided wit the 7am time.

=IF(OR(WEEKDAY(A4)=1,WEEKDAY(A4)=7),C4,C4<>TIMEVALUE("17:30"))

However now im trying to be really difficult and make it so that if the B or the C cell meets the conditional format the other one will also even if either meets its own criteria.

Sorry to be a pain.

 

[KyleG]

=if(or(weekday(a4)=1,weekday(a4)=7),b4,or(b4< Timevalue("7:00"),b4>timevalue("8:00")))

 

[Scott Huish]

You seem to be checking for 7AM-8AM is this correct, or is it 7AM - 8 PM?

 

[Scott Huish]

Assuming you mean 7AM-8PM, perhaps:

=OR(AND($A4<>"",WEEKDAY($A4,2)>5),AND(COUNT($B4:$C4),COUNTIF($B4:$C4,"<07:00")+COUNTIF($B4:$C4,">20:00")))

 

[KyleG]

Roster.xls
	A	B	C	D
172	Mon,7 Jan	7:30	17:30	1:00
173	Tue,8 Jan	7:30	17:30	1:00
174	Wed,9 Jan	11:45	21:00	0:30
175	Thu,10 Jan	7:30	17:30	0:45
176	Fri,11 Jan	8:00	17:45	1:00
177	Sat,12 Jan
178	Sun,13 Jan
179	Mon,14 Jan	7:00	17:45	1:00
180	Tue,15 Jan	7:00	17:30	1:00
181	Wed,16 Jan	7:30	17:30	1:00
182	Thu,17 Jan	7:30	17:30	1:00
183	Fri,18 Jan
184	Sat,19 Jan	7:30	12:30
185	Sun,20 Jan	7:30	9:00
186	Mon,21 Jan	7:00	17:30	1:00
187	Tue,22 Jan	8:00	17:30	1:00
188	Wed,23 Jan	7:30	17:30	1:00
189	Thu,24 Jan	7:30	17:30	1:00
190	Fri,25 Jan
191	Sat,26 Jan	7:30	17:30	1:00
192	Sun,27 Jan	7:30	12:30
Roster

8am is correct the B column represents start time and the C finish time. I cant get your formula to work so heres more information.

In the B column i have the conditional format formula
=if(or(weekday(a4)=1,weekday(a4)=7),b4,or(b4< Timevalue("7:00"),b4 >timevalue("8:00")))

In the C column i have the conditional format formula
=IF(OR(WEEKDAY(A4)=1,WEEKDAY(A4)=7),C4,C4<>TIMEVALUE("17:30"))

these work fine but where the B column conditional format kicks in i want the c column conditional format to kick in also. eg. Fri 11th conditional format would kick in for Finish time but the star time is ok so no conditional format. But because the Finish time format applies the start time should also apply. and the opposite should apply if true.

 

[Bjornar]

Try to use this Conditional Formatting formula entered in b4, copy format to c4 and down column b and c.

=if(or(weekday($a4)=1,weekday($a4)=7),$b4+$c4,or($b4< Timevalue("7:00"),$b4 >timevalue("8:00"),$c4<>TIMEVALUE("17:30")))

 

[KyleG]

Thanks Bjornar thats exactly what i was after.