Condition Formatting and formula

DKTRL

Board Regular
Joined
Jul 21, 2013
Messages
153
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
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
Joined
Jul 21, 2013
Messages
153
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
Joined
Jul 21, 2013
Messages
153
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
Joined
Jul 10, 2009
Messages
2,757
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
 

Forum statistics

Threads
1,137,060
Messages
5,679,382
Members
419,824
Latest member
Mercy kiara

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top