# Condition Formatting and formula

#### DKTRL

##### Board Regular
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

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### ukmikeb

##### Well-known Member
Hi

Formula in C7 :-
Code:
``=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 :-
Code:
``=WEEKDAY(C\$2,2)>5``
Format using Fill Colour.

hth

#### DKTRL

##### Board Regular
Hi

Formula in C7 :-
Code:
``=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 :-
Code:
``=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
Code:
``C8=IFERROR(C7-"18:00",0)``

Thanks

#### DKTRL

##### Board Regular
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

##### Well-known Member
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
Code:
``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 :-
Code:
``=C\$4="A/L"``

Format using Fill Colour.

hth

Replies
12
Views
714
Replies
8
Views
479
Replies
0
Views
206
Replies
0
Views
486
Replies
4
Views
1K

1,171,463
Messages
5,875,660
Members
433,146
Latest member
MinhTrang

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back