Conditional Formatting and Dates

Danielle Mor

1) I would like to have a conditional formatting rule for dates. 

I keep track of documents that expire annually for each member of my team. I want to input into a cell the date that the document will expire. When the document will expire in 60 days, I want that cell to turn yellow. When it will expire in 30 days, I want that cell to turn orange. When that cell is expired, I want it to turn red. 

2) I would also like to know how to set up my sheet so that if I add a new column (when a new person joins my team I will need to make a new column for them), it will copy the formatting done in the previous column automatically.

Paul Newcome

Make sure the column you are referencing in your conditional formatting rule is set as a date type column.

Paul Newcome

You would click on the conditional formatting button to apply conditional formatting to a column based on a date being within a certain number of days. They will run from top to bottom and will stop on the first one that triggers as true. If you build them out and find they need to be in a different order, you can click and drag to rearrange them.

To copy a rule, you would select the dropdown arrow to the left of the rule and select the option to clone. You can then tweak which column it is referencing and go from there.

RickyT

1) You can totally do this in the Conditional formatting section located here. ( Under the Star next to the highlighter)

It will pop up a window that looks like the image below where you can make all those conditions super easy

2) For your second point, If you have any conditional formatting you want to roll over I dont think you can just make a copy of a column thats formatted. If t was a formula you wanted to copy over you would just have to copy paste it into the new column. Fortunately making conditions inst to labor intensive so hopefully that helps!

Danielle Mor

Thank you Ricky. But there is no option in here that says "is less than 60 days" or 30 days, etc. Where is the option that will make the cell change color based on the date? For example, in my cell I have entered that the document will expire on 10/11/2023. I want to do a conditional format that will change this cell red because it will expire in 30 days.

But there is no option below for that.

Paul Newcome

Make sure the column you are referencing in your conditional formatting rule is set as a date type column.

Danielle Mor

That fixed it! Thank you.

Paul Newcome

Happy to help. 👍️