Conditional Formatting

Copper Contributor

Hi,

 

I'm looking to conditionally format cells based on a value (within the cell), while also being subject to dates (these dates are not in a cell)

 

basically:

 

 

if cell value is >=3 by 1/2/2024 then the cell will be red. 

is cell value is >=2 by 1/2/2024 then cell will be amber.
if cell value is >=3 by 1/1/2024 then cell will be amber.

 

i need the spreadsheet to update in line with 'todays date'

 

(I'm aware this is working in the past but will need to ensure it works before using for 2024/2025 data)

 

 

Thanks in advance!

1 Reply

@TamsinLeeLee 

You can achieve this using conditional formatting in Excel.

Here is how you can set it up:

  1. Select the range of cells that you want to format conditionally.
  2. Go to the "Home" tab on the Excel ribbon.
  3. Click on "Conditional Formatting" in the "Styles" group, then select "New Rule" from the dropdown menu.
  4. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".
  5. Enter the following formulas for each condition:

For red formatting (cell value >= 3 by 1/2/2024):

=AND(A1>=3, TODAY()>=DATE(2024,2,1))

Select the formatting style for red.

For amber formatting (cell value >= 2 by 1/2/2024 or >= 3 by 1/1/2024):

=AND(A1>=2, TODAY()>=DATE(2024,2,1))

Select the formatting style for amber.

=AND(A1>=3, TODAY()>=DATE(2024,1,1), TODAY()<DATE(2024,2,1))

Select the same formatting style for amber.

6. Click "OK" to apply the conditional formatting rules.

These rules will format the cells based on the conditions you specified, taking into account today's date. Make sure to adjust the cell references (e.g., A1) to match your actual data range. The text was created 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.