Conditional formatting with Date Picker

[MarchePR]

New member, hi!

I have encounter an issue that I can't seem to find a solution online. I currently have conditional formatting for when the due date to change color based on the following:

no fill = no date entered (rule: blank)
fill cell green = due date is 10+ days from today (rule: cell value greater than today+10)
fill cell yellow = due date is 10 or less from today (rule: cell value between today and today+10)
fill cell red = overdue (rule: cell value less than today)

In an attempt to enter dates faster, I inserted a date picker per this website instructions (Want to learn how to add a Date Picker Calendar to Excel?) . Why this method? I'm using a work computer and can't install anything so this was the method I found to bypass the admin restrictions. The date picker works but I lose all my conditional formatting rules; all cells get filled with green as you can see on E10:E11

Can someone help me with a code that where I can keep the date picker and also keep my conditional formatting rules? Appreciate any help

 

[Micron]

I'd start by making sure the picker returns date values and not text. It might have been obvious in your pic if you hadn't centered the values. Text would align left, dates and numbers, right.