Forum Discussion

twwareza's avatar
twwareza
Copper Contributor
Jan 18, 2021
Solved

Formula for conditional format with Months

I have a worksheet like this. The months is a date, but it shows only the month. I used Edate to add one month.  I am not able to work out how to create the formula to enable the conditional for...
excel
twwareza's avatar
twwareza
Copper Contributor
Jan 18, 2021

Hi big thanks. jukapil 

I noticed you used endtext ...but the row of months are actually a date, and I just displayed the Month. Would this matter or not?

I will have a bash at this in the morning. Heading out now. 

Thanks 

jukapil's avatar
jukapil
MCT
Jan 18, 2021

twwareza then on the right side you need to replace D$2 with Text(D$2,"MMM")

 

 

  • twwareza's avatar
    twwareza
    Copper Contributor
    Jan 18, 2021

    jukapil great. This kind of got it to work but now a few issues.

    As my months spans over 3 years it repeats the pattern across all years. 

    Also - odd that when no start/end dates are given - it still formats the cell. 

    This is the structure: 

    =OR(TEXT($C6,"MMM")=TEXT(H$4,"MMM"),TEXT($E6,"MMM")=TEXT(H$4,"MMM"))

    I guess its not the full solution to the problem. 

    Thanks for the guidance. 

    a. This picture shows the months repeated across all years

     

    b. This one shows the cell formatted even if start-end date is empty



    • jukapil's avatar
      jukapil
      MCT
      Jan 18, 2021

      twwareza Yes you are right because initially I saw the Month name. What you need to do, just add MMMYY in the formula and it will work

      • twwareza's avatar
        twwareza
        Copper Contributor
        Jan 20, 2021

        jukapil 

        Looks like when days span many months, it does not capture this with the current formula. This is a screenshot of what I mean.

        I tried adding >= or <= but that did not work. 

        Original

        =OR(

        TEXT($E7,"MMMYY")=TEXT(O$5,"MMMYY"),TEXT($G7,"MMMYY")=TEXT(O$5,"MMMYY"),

        TEXT($H7,"MMMYY")=TEXT(O$5,"MMMYY"),TEXT($J7,"MMMYY")=TEXT(O$5,"MMMYY"),

        TEXT($K7,"MMMYY")=TEXT(O$5,"MMMYY"),TEXT($M7,"MMMYY")=TEXT(O$5,"MMMYY")

        )

        Tweaked

        =OR(

        TEXT($E7,"MMMYY")>=TEXT(O$5,"MMMYY"),TEXT($G7,"MMMYY")<=TEXT(O$5,"MMMYY"),

        TEXT($H7,"MMMYY")>=TEXT(O$5,"MMMYY"),TEXT($J7,"MMMYY")<=TEXT(O$5,"MMMYY"),

        TEXT($K7,"MMMYY")>=TEXT(O$5,"MMMYY"),TEXT($M7,"MMMYY")<=TEXT(O$5,"MMMYY")

        ) 

        Any thoughts how to fix this?

        Reza

Resources