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

Copper Contributor

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.

broowe17_0-1712889820335.png

 

Any help would be greatly appreciated, thank you!

 

 

4 Replies

@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.

Thank you I appreciate the help! If I wanted cell B3 to fill in yellow when the date is within 1 month of cell F3, how would I do that please?

@broowe17 

You can modify the conditional formatting rule to check if the current date is within 1 month of the date in cell F3. Here's how you can do it:

  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()<=EDATE(F3,1))

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 F3 plus 1 month using the EDATE function. 7. Click on the "Format" button to choose the formatting options. You can set the fill color to yellow 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 yellow when the date is within 1 month of the date in cell F3. You can copy this conditional formatting rule to other cells as needed.