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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Bill PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
Okay, I figured it out, explanation coming shortly...


»bp
0
Bill PrewIT / Software Engineering ConsultantCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 PrewIT / Software Engineering ConsultantCommented:
Welcome, glad I was able to help, thanks for the feedback.


»bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.