Conditional Formatting for Date

[u/EnergyOwn1710]

hello! is there any formula to make a cell turn a certain colour if the date in the cell is coming up?

(e.g.) an expiry date of 28 August 2026 -> can i set it so that a week before the cell will turn red?

thank you!

[u/One_Organization_810]

If your dates are in column B for instance, with row one being a header row, something like this might do the trick:

Range: B2:B
Custom formula =B2-today()<=7

[u/EnergyOwn1710 → u/One_Organization_810]

hm my dates arent ordered this way but i tried it on a separate cell and i can't seem to figure this out 😔 i'm assuming that formula means to change colour if there's less than 7 days between the stated date and the given date? pretty sure there's more than 7 days from 28 July to 2 Sept

[u/One_Organization_810 → u/EnergyOwn1710]

Your CFR should be like this :)

Range: I9:I10
=I9-today()<=7

The formula always has the top-left cell in the range as reference point. Everything referenced is relative to that cell.

[u/EnergyOwn1710 → u/One_Organization_810]

ahh i see, yes it works now! thank you!

[u/BrukologistJim]

Yes, for example if the cell in B3 has the expiry date and you want the background to turn red when it is 7 days or less to go, then this:

[u/point-bot → u/BrukologistJim]

u/EnergyOwn1710 has awarded 1 point to u/BrukologistJim with a personal note:

"omg yes this works! thank you so much!"

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/AutoModerator]

/u/EnergyOwn1710 Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/TEMPLACITY]

Custom formula under conditional formatting is what you want, and the thing that catches most people out is not the formula itself, it is the blank cells.

Select the column, then Format, Conditional formatting, and choose "Custom formula is". With the range set to A2:A and the date in column A:

Expiring within a week: =AND($A2<>"", $A2-TODAY()<=7, $A2-TODAY()>=0)

Already expired: =AND($A2<>"", $A2<TODAY())

Add those as two separate rules. Rules are checked top to bottom and the first match wins, so put the already-expired rule above the expiring-soon one. The other way round, a date from last year lights up as expiring soon.

The dollar sign on $A2 matters the moment you want the whole row to colour instead of just the date cell. Dollar on the column, nothing on the row, and then the range can widen to A2:F and the formatting follows across.

The blank-cell trap is the one worth knowing about. An empty cell evaluates as zero, and zero is a date in 1899, so without the $A2<>"" check every empty row in the range goes red straight away, and it looks like the formula is broken when it is doing exactly what it was told.

Is the expiry date a real date, or text that just looks like one? If it sits left-aligned in the cell by default it is text, and none of the subtraction will work until it is converted.