How to get formula to work in Excel Conditional formatting

Need to do an Excel conditional format by comparing 2 date columns. I can't seem to get it to work.
I tried the following formula:      

 =($K$39<=$S$39) hi-light a cell in Column k with the color green.

Any help would be appreciated.
Thank you...
Rick
ES-ComponentsAsked:
Who is Participating?
 
tnayakCommented:
One of the problems was that the dates in column K are strings formatted as a date.  You can convert the data using the DATEVALUE function.  It still doesn't work in your workbook – there must be another issue.  I copied the data into a new workbook, converted the dates in column K and it worked (see attached)
OrdersInTroubleFinalv2.xlsx
0
 
Bill PrewCommented:
That should work okay, assuming you set the color options you wanted when creating that rule.  What do you see happening, can you upload a sample workbook?  I did a quick test here on what I thought you would have done there and it seemed to work...


»bp
0
 
Bill PrewCommented:
If you were trying to apply that to the whole column then you would want:

=$K1<=$S1

or something like that.  And make sure that you select the whole column for it to apply to.


»bp
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ES-ComponentsAuthor Commented:
I will upload the file. I cleared the formula from the worksheet.

Perhaps you can get it to work, as when I try nothing happens.

Thanks...
Rick
OrdersInTroubleFinal.xlsx
0
 
Bill PrewCommented:
It looks like the values in column K aren't actually dates, how did they get in there?


»bp
0
 
ES-ComponentsAuthor Commented:
On my spreadsheet the format in column K is set to date: 2/8/18.  If you look at
Rows 39 through 42 and compare K to S you will see where that condition is true.

I am not sure why Column K is not showing a date format on the sheet I sent you.

Rick
0
 
Bill PrewCommented:
If I select all rows and columns in your sheet, and paste it to a new sheet in a new workbook, then the following formula works as desired.

=DATEVALUE($K1)<=$S1

So one problem is that column K is text, not true date values.

Not sure what the other problem is, could that sheet or your workbook have some protection on it?


»bp
0
 
Bill PrewCommented:
Okay, I figured it out, explanation coming shortly...


»bp
0
 
Bill PrewCommented:
Okay, I noticed that the cells on the current sheet have fill color of black on them, and this seems to be causing a problem.  To correct I first created the rule for the whole column K as follows:

sshot-420.png
But I didn't see any colors yet, even though I should have.  So I selected a cell in column K and looked at it's Fill property, seeing:

sshot-418.png
I didn't like the black fill color.  So I selected all of column K, changed the fill color to something else, then set it to Automatic:

sshot-419.png
Saving that and the colors appeared as I expected.

OrdersInTroubleFinal.xlsx


»bp
0
 
ES-ComponentsAuthor Commented:
Thank you so much Bill for all your help. I will click Best Solution for you!!!!
I will make the changes, now that I see what you did.

Thanks again!
Rick
0
 
ES-ComponentsAuthor Commented:
Fantastic Job! I would have been here working at this for days. Thank you for all your help!!
0
 
Bill PrewCommented:
Welcome, glad I was able to help, thanks for the feedback.


»bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.