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

0
votes

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

2
excel
Have you tried anything yet? Sounds like you have a pretty good handle on where to start. Also, you don't define certain terms like "month." Is that 30 days? What date would you say is "one month" after February 1? What day is "one month" after January 30? Think through your problem and try to solve it yourself first. Then come back here and ask once you're stuck on your best efforts. – Marc

2 Answers

1
votes

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.

0
votes

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.