Condition Formatting and formula

[DKTRL]

Dear All

I think this is very simple, but to my little knowledge i don't know how to set the formula. can please give me some help.

What i want is as follow.

C2:AC11 is my data

and from C2:AC2 is the date (dd ddd ) from first day of the month to end day of the month

and from C7:AC7 is the Time. by default it set as (18:00PM).

May i know how to set a formula that can automatically change the corresponding cell time to (13:00PM) if within C2:AC2 the day is on the weekend ?

For example, if O2 = "14 Sat" than O2:O11 will be hight to yellow and O7 = (13:00PM )


Any help will be appreciate.

Thanks

 

[ukmikeb]

Hi

Formula in C7 :-

=IF(WEEKDAY(C2,2)>5,TIMEVALUE("13:00:00"),TIMEVALUE("18:00:00"))

and dragged across to AG7.

Conditional formatting :-
Select C2 to AG11
then Conditional Format using formula :-

=WEEKDAY(C$2,2)>5

Format using Fill Colour.

hth

 

[DKTRL]

Hi

Formula in C7 :-

=IF(WEEKDAY(C2,2)>5,TIMEVALUE("13:00:00"),TIMEVALUE("18:00:00"))

and dragged across to AG7.

Conditional formatting :-
Select C2 to AG11
then Conditional Format using formula :-

=WEEKDAY(C$2,2)>5

Format using Fill Colour.

hth

Dear unkmikeb

Thanks for your respond. i have try and it work great ! but i have another problem need your help. i am getting this error on C8:AC8 when the day is foll on weekend because i am applying the formula

On C8:AC8

C8=IFERROR(C7-"18:00",0)

Thanks

 

[DKTRL]

Sorry can i add 1 more question, i would also want to highlight the cell if there is a special text within C4:AC4. for example


if D4 = A/L, than the entire col from D2:D11 highlight to green.

Sorry to trouble.

Thanks

 

[ukmikeb]

Thanks for your respond. i have try and it work great ! but i have another problem need your help. i am getting this error on C8:AC8 when the day is foll on weekend because i am applying the formula

On C8:AC8

C8=IFERROR(C7-"18:00",0)

Thanks

What are you trying to do on row 8?
Do you want a zero when it is a weekend?

Sorry can i add 1 more question, i would also want to highlight the cell if there is a special text within C4:AC4. for example


if D4 = A/L, than the entire col from D2:D11 highlight to green.

For Conditional Formatting for "A/L" in row 4 :-
Select C2 to AG11
then Conditional Format using formula :-

=C$4="A/L"

Format using Fill Colour.

hth