Conditional Formatting Based on Dates

Question

Hi All,

I have a spreadsheet full of compliance expiration dates. There are multiple worksheets in the same file. I would like to apply a conditional format so when the expiration date is a certain number of dates out, then it turns a certain color. I see the presets for 2- and 3-color scales, but the issues are that I want four colors, and I don't want the color to gradually change, as it's doing now (see scale below - I don't want that). I want the colors to change immediately at the following intervals:

60 days out - yellow

30 days out - orange

14 days out - red

0 days (or expired) - black with white text.

I think I'm on the right track with the conditional formatting, but this type of setting is beyond my knowledge. Please help!

Thank you very much!!

Amanda

Answer 1

Hi Ashish,

As I explained, I am unable to provide the file.

Thank you for trying.

Amanda

Answer 2

Hi,

OK, so they are actual dates.  I will need to see the workbook.

Answer 3

Hi Ashish,

Sorry I missed answering your previous question regarding the filter. When I click the sort dropdown for the Hull column, this is what I see.

Since I am on a work computer with strict controls, I am unable to upload to OneDrive. Can you please tell me how to correct the issue you are getting at?

Thank you!

Amanda

Answer 4

Hi,

Formatting only changes the display - not the data type.  Upload the workbook to OneDrive and share the download link here.

Answer 5

Hi Ashish,

The left-aligned dates are just how this particular forum pasted them in there. They are indeed formatted as "short date".

I also do not think the formatting is an issue because I previously had conditional formatting applied and it worked out fine, except that it changed the colors gradually.

Not sure if this makes a difference, but I also have validation applied so users can only type certain formats into the cells. For example, if they typed 12/2015 it wouldn't accept that.

Thanks again for your help!

Amanda