CODEWITHSUNDEEP
excel
Steve Allen
Steve Allen

Reputation: 1

How to conditional format a cell based on the date entered in an adjacent cell

I am setting up a spreadsheet that has dated in column M. Cells in column P need to be

Green if today's date is no greater than eleven months of the date in column M; Amber if today's date is within one month of the date in column M; and Red if today's date is one year over the date in column M

Please help

Upvotes: 0

Views: 176

Answers (2)

Justin Burgard
Justin Burgard

Reputation: 470

Two conditional formulas. On the first, something like =DATEDIF($M2, TODAY(), "M") <= 11. Then the same with one month, then with 12 months.

Just make sure the target formula is column P, and reference column M.

Upvotes: 1

Sam
Sam

Reputation: 64

Conditionals for column P should be:

=DATEIF(M2, TODAY(), M)<=11 for Green

=DATEIF(M2, TODAY(), M)<=1 for Amber

=DATEIF(M2, TODAY(), Y)>=1 for Red

Assuming your first row in each column has a title, hence using M2. Make sure you pay attention to the order and use the "Stop if True" option to make sure the cells you want Amber aren't Green.

Upvotes: 0

Related Questions