0

I was wondering if there is any way (formulas) that can change a cell's color when we are approaching the date previously entered in a cell. What I am looking for is a way to notify myself of upcoming milestones so that I don't miss deadlines.

For example: if I forecasted that I need to apply for a building permit on 02/17/26 (data already in cell), is there any way that I can format that cell to change colors when we are approaching that date?

Yellow for 30 days before the date & red for 10 days before that date.

Attached is a screenshot to help explain. Any help would be greatly appreciated due to my below average Google Sheet skills

enter image description here

I searched Google for existing formulas to change a cells color. The only result I could find were formulas that changed cells based on distance from today's date not approaching future dates

Improve this question
6
  • 1
    "The only result I could find were formulas that changed cells based on distance from todays date not approaching future dates" - what's the difference? Whether you check the difference between today and a date that is two weeks into the past, or two weeks into the future - that should only result in a different sign in the resulting value, no? Please show us what you found/tried, instead of just telling us something so vague. Commented Oct 1 at 12:24
  • You can use Conditional Formatting to change the color of your cell, just choose custom formula then paste the formula =IFERROR(AND(A1-TODAY()<=30, A1-TODAY()>10),"Invalid Date") for color Yellow, then another custom formula for color Red =IFERROR(AND(A1-TODAY()<=10, A1-TODAY()>=0),"Invalid Date") Commented Oct 1 at 12:45
  • I very well could be asking my question incorrectly. I found this formula.. =AND(TODAY()-D1>90, TODAY()-D1<=120, ISDATE(D1)). But the reason I didn't believe that this would work is because my data is forecasted out over months. So what i'm searching for is a way to notify myself of upcoming milestones so that I don't miss deadlines Commented Oct 1 at 12:56
  • I tried =IFERROR(AND(A1-TODAY()<=30, A1-TODAY()>10),"Invalid Date") but nothing happened @ELSRY. I wonder if entered the data in incorrectly & google sheets is viewing the data as numbers not a date Commented Oct 1 at 13:05
  • Have you tried the formula in conditional formatting and set the color? you can visit the hyperlink for a quick guide on how to use conditional formatting. Commented Oct 1 at 13:08

1 Answer 1

Reset to default
2

Use conditional formatting custom formula rules like these:

blue: =A1 > today()

red: =A1 > today() - 30

yellow: =A1 > today() - 10

See Use conditional formatting rules in Google Sheets.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.