conditional format a formula result

bmuddy2

Active Member
Joined
Jun 22, 2004
Messages
398
cell AD1 contains "1/2/2006 3:00:00 PM".
cell AD2 contains =AD1
cell AD2 is formatted as special "ddd" , this displays what day it is.
mon , tue, wed, etc...

column AD has the formula copied down 1000 rows.

now the question is: using conditional formatting, how do i look in column AD and find all the "Fri" so i can format there cell's gray?

i have tried the simple formatting, but it seems to be looking at the formula's.


just to add a little , i am using cell AE1 as the cell to look at for what day i am trying to highlight. AE1 contains "Fri" but next week might contain "Mon".

hope i explained this well enough.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Use Weekday() function in your CF formula

Select Formula Is from the CF dialogue and enter the formula

e.g. =WEEKDAY($AD1)=6

I am confused about whether it is AD or AE column you have the formula in so adjust formula above as necessary.
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Highlight cell AD1, select the Format menu > conditional formatting. Then for Condition 1 select "Formula Is" in the list box. Then in the next box enter the formula =WEEKDAY(AD1)=6 then click on the Format button and set you format (grey pattern). Click OK. Click OK. Then using the format painter paint that format all the way down the column. All cells in that column containing a Friday date will then be grey.
 

bmuddy2

Active Member
Joined
Jun 22, 2004
Messages
398

ADVERTISEMENT

after trying to figure out what was wrong here , i notice i have given the wrong information..oop's
garbage in garbage out .... so let me correct my self now.

cell AD1 contains "1/2/2006 3:00:00 PM"
cell AD2 cnotains "5/4/2006 8:47:00 PM"
and these dates continue on down for about 1000 rows, all in column AD.

now in the next column begining with AE1 i have =AD1.
i then dragged this down about 1000 rows and formated coulmn AE as special "ddd" so i can see that day of the week.

for example AD1 has
"1/2/2006 3:00:00 PM" displayed in it.
AE1 has the formula "=AD1" typed in it but will display "Mon".

now in cell AF1 i have typed "Fri".

now my goal was to use conditional formatting on column AD to highlight all the "Fri" 's.

i planned to have the conditional formatting look at cell AF1 to find the text to look for. this is because i may not always want to see "Fri" , i may need to see "Wed" or some other day and would like to just change the contents of cell AF1.

i hope i have explained myself better.
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
set your conditional formatting with AE1 highlighted. Use Barry's suggestion, Formula Is: =TEXT(AE1,"ddd")=$AF$1 and paint down column AE
 

Forum statistics

Threads
1,137,193
Messages
5,680,086
Members
419,880
Latest member
suarezprado

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