Change the status of a cell depend on the date of another cell

[ENI]

Hi, I have added status on column A (ongoing, closed, not started,Waiting to receive the product ) and different dates on column B (received date). How can i use conditional formating on column A to change the status from ongoing or not started to "Alert" if the received date on column B is older than 30 days? But not change the status if the status is close (even though the date is older then 30 days).

 

[Joe4]

Welcome to the Board!

I think you may be confused about Conditional Formatting. Conditional Formatting only affects the formatting of a cell (color, font size, etc). It does NOT affect the value shown in the cell.
Only formulas and VBA can change the contents shown in the cell.

What you would use Conditional Formatting for here is what color to make the cell dependent on the value being shown in the cell (i.e. if the cell value is "Ongoing", make the cell orange, if the cell value is "Closed", make the cell green, etc).

So do you really need help with Conditional Formatting, or with formulas in column A?
If you need help with formulas in column A, what are the formulas in there right now?
If there are hard-coded values in column A and not formulas, but you want to change the value in column A depending upon conditions, that will require VBA (as any cell can have EITHER a formula or a hard-coded value, but never both at the same time).

 

[ENI]

Hi Joe,

Thank you for your reply!
I don't have formulas now on the column A, I have just i.e. if the cell value is "Ongoing", make the cell orange, if the cell value is "Closed", make the cell green, etc). How can i make the cell from orange (ongoing ) to red?
I don't mind to use formulas as well, just need to make this work (have some red color yelling at me if we are more then 30 days late.
What would you suggest?

Thank you in advance!

 

[Joe4]

If you want to change the color of column A to red when the value in column A is "Ongoing" and the date in column B is more than 30 days past, then choose the "Use a formula to determine which cells to format" option of Conditional Formatting and enter this formula (for row 2, in this example):

=AND($A2="Ongoing",TODAY()-$B2>30)

and then choose the red cell color formatting option.