Forum Discussion

broowe17's avatar
broowe17
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 spreadsheet there are two columns. One is the date the program opens and the other is when the program closes.

 

I would like cell B3 to fill in green when the current date is past the date in cell F3 and before the date in cell G3.

 

Any help would be greatly appreciated, thank you!

 

 

excel

8 Replies

Sort By
  • HoffyC's avatar
    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's avatar
      SergeiBaklan
      MVP
      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's avatar
        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.  

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Apr 12, 2024

    broowe17 

    You can achieve this using conditional formatting in Excel. Here's how you can set it up:

    1. Select cell B3.
    2. Go to the "Home" tab on the Excel ribbon.
    3. Click on "Conditional Formatting" in the Styles group.
    4. Choose "New Rule" from the dropdown menu.
    5. In the New Formatting Rule dialog box, select "Use a formula to determine which cells to format."
    6. Enter the following formula in the formula box:

    =AND(TODAY()>=F3, TODAY()<=G3)

    This formula checks if the current date (TODAY()) is greater than or equal to the date in cell F3 and less than or equal to the date in cell G3. 7. Click on the "Format" button to choose the formatting options. You can set the fill color to green or any other color you prefer.

    1. Click "OK" to apply the formatting.
    2. Click "OK" again to close the New Formatting Rule dialog box.

    Now, cell B3 will fill in green when the current date is past the date in cell F3 and before the date in cell G3. You can copy this conditional formatting rule to other cells as needed. The text and steps were edited with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources