not sure if this is the right place to ask, but I'm needing help formatting Cells in excel.

I have a future date in a cell.
I want that cell to change colors 10 days or so before that date, then change again on that date.

for example:
in cell C9, I have the date 10/28/24

on 10/18/24 I want that cell to automatically be filled yellow and stay yellow until 10/28/24. And on that date, it would change to red and stay red permanently.

I know I have to create a new rule in conditional formatting.

I'm assuming I'll have to use TODAY() somewhere as part of the rule?

3 ...

Report Post

Posted by BabySam

Member since Oct 2010

1528 posts

Posted on 6/20/24 at 4:35 pm to Nutriaitch

Chatgpt that shite

0 ...

Report Post

Posted by mdomingue

Lafayette, LA

Member since Nov 2010

37680 posts

Posted on 6/20/24 at 6:16 pm to Nutriaitch

Try this

ETA: Obviously the range needs to be the cells you want to format conditionally.

This post was edited on 6/20/24 at 6:25 pm

1 ...

Report Post

Posted by boomtown143

Member since May 2019

9407 posts

Posted on 6/20/24 at 6:25 pm to Nutriaitch

conditional formatting

like above

This post was edited on 6/20/24 at 6:26 pm

0 ...

Report Post

Posted by Nutriaitch

Montegut

Member since Apr 2008

9654 posts

Posted on 6/20/24 at 8:00 pm to mdomingue

i tried conditional formatting
> or equal to Today()+10
< or equal to Today()+10

both just highlights the cell no matter what damn date is in it. even dates next year.

1 ...

Report Post

Posted by lostinbr

Baton Rouge, LA

Member since Oct 2017

11670 posts

Posted on 6/20/24 at 11:16 pm to Nutriaitch

quote:
i tried conditional formatting
> or equal to Today()+10
< or equal to Today()+10

both just highlights the cell no matter what damn date is in it. even dates next year.

Try going to 2 new cells (you can put them on a new sheet and hide them if you want) and making them:

Cell #1: =today()+10
Cell #2: =today()

Then your conditional formatting will reference the cells rather than putting the formulas in the condition.

First rule will be:
If [formatted cell] <= [cell #1], [yellow fill]

Second rule will be:
If [formatted cell] <= [cell #2], [red fill]

Or you could use “between” for the first one.. but it should work as long as your “red fill” rule is last.

I say try this way (calculating the trigger dates in separate cells) because sometimes Excel gets fricky when you do formulas in conditional formatting rules. I think there’s a different syntax you have to use but honestly, I find it easier to just use cell references.

Just make sure the references are absolute (not relative) so they don’t get jacked up.

Page 1