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

Resources