Conditional Formatting based on Date

Nicole Hodges

All,

I would like to create a conditional formatting based on a date.

Use case below:

Column A is a date - I would like Column B to turn yellow if it is under 10 days from Column A date, turn orange if past 15 days and turn red if past 15 days.

Is this possible? Would I need to create a formula? Doesn't look like I can base this solely on conditional formatting.

Genevieve P.

Hi @NBudd

You could write a formula to do something similar if you wanted to have status balls change depending on the date (this may help if you wanted to pull a Report based on the colours), but it's also possible to do just with conditional formatting.

The order in which you write your conditional formatting rules matters, so first start with the Yellow, then Orange, then Red. Here's a picture of how I set up 3 rules to colour the background of Column B:

You'll see that I put "If Date is in the last 15 days" before the red rule, because then the Red one will only happen if the date is older than the last 15 days. This is where you can find the date rules:

Let me know if you have any questions about this! You can read more about Conditional Formatting in our Help Center (see here).

Cheers,

Genevieve

Genevieve P.

Hi @NBudd

You could write a formula to do something similar if you wanted to have status balls change depending on the date (this may help if you wanted to pull a Report based on the colours), but it's also possible to do just with conditional formatting.

The order in which you write your conditional formatting rules matters, so first start with the Yellow, then Orange, then Red. Here's a picture of how I set up 3 rules to colour the background of Column B:

You'll see that I put "If Date is in the last 15 days" before the red rule, because then the Red one will only happen if the date is older than the last 15 days. This is where you can find the date rules:

Let me know if you have any questions about this! You can read more about Conditional Formatting in our Help Center (see here).

Cheers,

Genevieve

Nicole Hodges

Thank you so much!!!

Genevieve P.

No problem! Glad this worked for you 🙂

beatea

Hello. I have the same question but was not able to apply format based on column A to column B. How do I compare the two dates?

Genevieve P.

Hi @beatea

What column type is Column A? Could you provide a screen capture of your sheet (but block out sensitive data) and identify what you want the conditional formatting to do?

Thanks,

Genevieve