Format cell based on months that have elapsed

[pww85]

Hi there,

Looking for some help with a formatting on excel.

I have a cell which contains a date an item was received (D2 in this example).

I would like another cell to change colour based on how many months have elapsed since that item was received (needs to be months).

I need the cell to be green if less than 5 months have elapsed, yellow if between 5 to 6 months have elapsed and red if more than 6 months have elapsed.

I have one rule with =DATEDIF($D2+1,TODAY(),"m")<5 to make less than 5 months be green.

I have a second rule with =DATEDIF($D2+1,TODAY(),"m")>6 to make more than 6 months red.

I am struggling to format between 5 and 6 months to be yellow?

Any help would be appreciated.

Thank you!

[etaf]

can you attach a sample spreadsheet
using your formula
=OR(DATEDIF($D2+1,TODAY(),"m")=5,DATEDIF($D2+1,TODAY(),"m")=6)
Otherwise
If you order and STOP if true
so >6 should be first in the list and then STOP if TRUE
followed by
DATEDIF($D2+1,TODAY(),"m")>4
Stop if true

[Pete_UK]

Conditional Formatting follows a hierarchy, depending on the order that the conditions appear, so if neither of the two conditions that you have outlined are satisfied, then the elapsed time must be between 5 and 6 months (assuming it is not blank). If you just applied normal formatting of yellow to the cell, then it will show as such for 5 or 6 months. Just to be sure, you could apply a third CF condition which tests for $D2 not being blank, but make sure this condition is third on the list:

=$D2<>""

Alternatively, you could test specifically for your condition like this:

=AND(DATEDIF($D2+1,TODAY(),"m")>=5, DATEDIF($D2+1,TODAY(),"m")<=6)

Hope this helps.

Pete

[pww85]

can you attach a sample spreadsheet
using your formula
=OR(DATEDIF($D2+1,TODAY(),"m")=5,DATEDIF($D2+1,TODAY(),"m")=6)
Otherwise
If you order and STOP if true
so >6 should be first in the list and then STOP if TRUE
followed by
DATEDIF($D2+1,TODAY(),"m")>4
Stop if true

That formula works, however I have one slight issue.

Everything 5 months or less does turn green.

Also everything over 5 months turns yellow but this somehow stays yellow until 7 months?

Then anything over 7 months turns red instead?

I cant attach my sheet or a sample unfortunately due to it being on a work machine, apologies.

[pww85]

Managed to sort the problem

I changed =AND(DATEDIF($D2+1,TODAY(),"m")>=5, DATEDIF($D2+1,TODAY(),"m")<=6) to =AND(DATEDIF($D2+1,TODAY(),"m")>=5, DATEDIF($D2+1,TODAY(),"m")<=5)

also changed =DATEDIF($D2+1,TODAY(),"m")>6 to =DATEDIF($D2+1,TODAY(),"m")>5

Seems to have solved the issue and now the colours change as needed.


Many thanks for your help and to those who replied.

[etaf]

you are welcome