Search code examples
excelvbaformula

HIghlight month name less than 120 days using conditional formatting

I have month's name from January to December in C5:R5. They do not have dates tied to them, just month names.

What is the conditional formatting formula I can use to highlight the month which is older than 120 days?

Do I format them as something specific too? =TEXT(TODAY(),"mmmm")=C5 i used this but it highlighted current month

I wanted to highlight "project values" if month is older than july. example can this be possible with my above idea?

Solution

  • Yes, it is possible, but I would advise you not to enter the names of the months, but to convert a normal date into a month name, as you can see in my screenshot:

    enter image description here

    Column "A" contains the date.
    Column "B" contains the same date, but I use cell formatting to show the month name (format "mmmm", as you guessed correctly).
    Column "C" contains the formula =TODAY()-Bx>=120 for checking the date values.
    Column "D" contains the formulaText of column "C".

    It's obviously the idea that you use the formula of the "C" column as an input for your conditional formatting.