Excel Formula for Conditional Format

katkth7533

New member
Joined
Jul 29, 2014
Messages
17
Reaction score
0
Points
0
Hi everyone! I was hoping that someone could help me with a formula. I am drawing a blank. In Excel, I want to do conditional formatting on a cell based on the value of another cell. For example: in cell A1, I have text. I want this cell to have a grey background if the value of cell B1 is less than or equal to 1/31/14. How would I do this? Any suggestions? Thank you for your help!
 

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
Good morning,

Select the range in column A and create a new conditional formatting rule. Use:

=b1<=41670

You might be able to enter an actual date, but this is the numerical equivalent.

Best of luck,
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
You can use

=B1<=DATE(2014,1,31)

or

=B1<--"31-Jan-2014"
 

katkth7533

New member
Joined
Jul 29, 2014
Messages
17
Reaction score
0
Points
0
Thank you Bgore. This worked using the numerical equivalent. Do you know if it is possible to apply conditional formatting if a date is between a range? For example, cell A1 will turn grey if cell b1 is between 1/1/14 and 2/1/14?
 

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
Hello again,

You're very welcome. That is also possible. Bob's format seems cleaner so I will use it:

=AND(B1<DATE(2014,1,1),B1>DATE(2014,2,1))

Also, if the date range is dynamic, you could place the begin and end dates in cells somewhere and reference those.

Hope this helps,
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
I think you mean

=AND(B1>=DATE(2014,1,1),B1<DATE(2014,2,1))

it cannot be before 1st Jan AND after 1st Feb, there is no overlap there.
 
Last edited:

katkth7533

New member
Joined
Jul 29, 2014
Messages
17
Reaction score
0
Points
0
Yea :-\, sorry I was in a hurry.

Thank you. I will try this now. I gratefully appreciate all the help. It is amazing how you lose all this info when you don't use it. Have a wonderful day!
 

katkth7533

New member
Joined
Jul 29, 2014
Messages
17
Reaction score
0
Points
0
I tried both of these and it is not working. UGH! So frustrating! I tried several different formulas too. I am sure I am doing something stupid but can't figure it out. Getting function errors and I have double-checked the syntax and the cell references and all looks correct. I have attached my file to show what I am doing. On the tab titled "PIPELINE", in cell Z2, I am trying to add a conditional format that if the date in cell BF1 is between 9/30/14 and 10/31/14 the left margin of the Z2 cell will be a red border. Nothing seems to work. Even if I don't get an error message, the border doesn't appear. Perhaps you see something that I am doing wrong. The date in cell BF1 is a link to a cell on the tab called "DATA ENTRY". My thought with this spreadsheet is that an end user will enter required info on the "DATA ENTRY" sheet that populates the spreadsheet in "PIPELINE" which is a visual presentation of the data. That red line is a status date and will move each month.
 

Attachments

  • PipelineNEWFORMAT.xls
    172.5 KB · Views: 16

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
Hello again,

Please see the attached file.

Hope this helps,
 

Attachments

  • PipelineNEWFORMAT_sample.xls
    172.5 KB · Views: 22

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
This formula works for me

=AND(B1>=DATE(2014,9,30),B1<DATE(2014,10,31))
 
Top