Follow Up on Formula Question asked last week | Excel forum

Hi Marius,

My apologies to reach out to you again.

Last week, you gave me a solution to my question below.

I would like to create the following formula and also add in conditioning format

If due date is less than 6 months then return 3-Low
If the due date is greater than or equal to 6 months then return 2- Medium
If due date is greater than 1 year then return 1- High

Solution:
=IF(B2>=EDATE(TODAY(),12),1,IF(B2>=EDATE(TODAY(),6),2,3))

This formula assumes that column B holds the dates. The EDATE function looks at dates exactly x months away from a given date (in this case TODAY().

This formula will always assign 1 for >= 1 year, 2 for >= 6 months up to 1 year, and 3 for less than 6 months.

Afterwards, you could apply conditional formatting based on 1, 2 or 3.

Having tested this on my file, it does not appear to be working.

e.g. I have a date as 08 Jul 20 (Date format is DD/MMM/YY) which I assume would return 1 however this is not the case. It only returns the number 3 all the way down the columns.

Also, I cant seem to apply conditional formatting based on 1, 2 or 3 hence appreciate any pointers on this area too.

Appreciate if you can kindly check and confirm.

Many Thanks
Kay