Forum Discussion

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Apr 11, 2024

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

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.

3 Replies

  • broowe17's avatar
    broowe17
    Copper Contributor
    Apr 14, 2024
    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?
    • NikolinoDE's avatar
      NikolinoDE
      Platinum Contributor
      Apr 15, 2024

      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.