Conditional Formatting - Everyones Bane

B

Bjornar

Board Regular

Joined

Jan 9, 2008

Messages

84

• Jan 9, 2008

• #2

Try this for your conditional formatting formula:

PHP:

=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.....

 

Last edited: Jan 9, 2008

Upvote 0

B

Bjornar

Board Regular

Joined

Jan 9, 2008

Messages

84

• Jan 9, 2008

• #3

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:

PHP:

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

 

Upvote 0

K

KyleG

Well-known Member

Joined

Jun 12, 2004

Messages

629

Office Version

1. 365

Platform

1. Windows

• Jan 9, 2008

• #4

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.

 

Last edited: Jan 9, 2008

Upvote 0

K

KyleG

Well-known Member

Joined

Jun 12, 2004

Messages

629

Office Version

1. 365

Platform

1. Windows

• Jan 9, 2008

• #5

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

 

Upvote 0

Scott Huish

MrExcel MVP

Joined

Mar 17, 2004

Messages

20,548

Office Version

1. 365

Platform

1. Windows

• Jan 9, 2008

• #6

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

 

Upvote 0

Scott Huish

MrExcel MVP

Joined

Mar 17, 2004

Messages

20,548

Office Version

1. 365

Platform

1. Windows

• Jan 9, 2008

• #7

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")))

 

Upvote 0

K

KyleG

Well-known Member

Joined

Jun 12, 2004

Messages

629

Office Version

1. 365

Platform

1. Windows

• Jan 14, 2008

• #8

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.

 

Upvote 0

B

Bjornar

Board Regular

Joined

Jan 9, 2008

Messages

84

• Jan 15, 2008

• #9

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

PHP:

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

 

Upvote 0

K

KyleG

Well-known Member

Joined

Jun 12, 2004

Messages

629

Office Version

1. 365

Platform

1. Windows

• Jan 15, 2008

• #10

Thanks Bjornar thats exactly what i was after.

 

Upvote 0