Forum Discussion

Copper Contributor

Apr 12, 2024

Change cell colour when current date is greater than one cell date and less than another cell date

Hi, I am creating a spreadsheet to provide information on sports programs throughout the year. I want to include a feature for when a program is open, a cell will fill in green. On the spreadsh...

excel

HoffyC

Copper Contributor

Jan 28, 2025

I need to change the colour of a cell based on whether the date is today or over, within 30 days of today or 60 days of today.

I'm using the formulas -

=AND(C6:AY25>(TODAY()+30),C6:AY25<(TODAY()+60))

=AND(C6:AY25>TODAY(),C6:AY25<=(TODAY()+30))

But it isn't working.

Can anyone help?

I also need to create one for today's date or over.

I'm trying to create a traffic light system for training dates, green, within 2 months away before renewal, yellow within one month before renewal, and red overdue.

Thank you for any help :)

SergeiBaklan
Diamond Contributor
Jan 29, 2025
HoffyC , formula shall be like

=AND(C3 >(TODAY()+30),C3<(TODAY()+60))

i.e. you use in it first cell from the range to which conditional formatting is applied ($C$3:$AY$25). With that conditional formatting iterates each cell in the range applying the formula. Since we use relative reference in the formula, internally in formula will be used C3, D3,...AY3, C4,...AY25.

That the same as you enter above formula below you range and drag it down and to the right if you'd like to test returned values.
- HoffyC
  Copper Contributor
  Jan 30, 2025
  Brilliant, thank you Sergie, that worked.
  Would you be able to help me with the final formula? any date that has expired the cell turns red?
  Your help has been exceptional.
  - SergeiBaklan
    Diamond Contributor
    Jan 31, 2025
    You may add one more rule with formula like
    
    = C3 < TODAY()
    
    for the "red" formatting.