Conditional Formatting Issue

  • Hi Guys, how is everything, I am seeking your advice here please, why Conditional Formatting didn’t work for $D3:$D13 range? It was meant to highlight the time duration if it is greater than 0:10:00, taking into account that the range values come from formulas and that the cells are formatted as numbers for reporting purposes, any help please?

  • I need the cells to be highlighted if the duration is longer than 0:10:00 minutes
    What happened that the conditional formatting doesn't work
    As you could see the first cell has 0:07:34 as value, however, it is highlighted as if it is longer than 0:10:00 with the conditional formatting I set
    Which is incorrect

  • Hello,


    Two quick remarks for the sheet : Category Template :wink:


    1. Avoid ALL Merged Cells


    2. Make sure to correctly and consistently Format your Column X or AA


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • صباح الخير


    Hello Magdoulin,


    Is the correct format in Column X ( Sheet Category template ) combined with the new formula in cell D3 ( Sheet Category Final Report ) allowing the conditional formatting to operate 'normally' ...? :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • صباح الخير


    Hello Magdoulin,


    Is the correct format in Column X ( Sheet Category template ) combined with the new formula in cell D3 ( Sheet Category Final Report ) allowing the conditional formatting to operate 'normally' ...? :wink:



    I'm not really sure but I think so
    The thing is, I fully understand the complexity caused by the merged cells, but this's inevitable actually as the report is generated by this way from the system.

  • Hello,


    In the message # 5 above ... your test file is fully operational ...:wink:


    Take the time to understand how the Format used in Column X is impacting on the formula used for Conditional Formatting ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • That didn't work because the times in column D are times as string. Change the formula to the following one and it will work.

    Code
    =AND(D3<>"",D3*1>TIMEVALUE("00:10:00"))



    It didn't work with me I'm afraid, have you tried it on my sample file and it worked with you?

  • Hello,


    In the message # 5 above ... your test file is fully operational ...:wink:


    Take the time to understand how the Format used in Column X is impacting on the formula used for Conditional Formatting ... :smile:



    Well, the modifications I can see you have applied that you changed the column X and AHT column format to be as time mm:ss, which is affordable, and you have unmerged the cells in the template sheet which is something we won't be able to do all the time for the fact that the report is generated that way from the system.


    And to be honest, I'm not really sure why the merged cells should affect the conditional formatting in another sheet


    I don't know where is the gap here because I have tried to change the formatting of the two columns to be time mm:ss keeping the merged cells as they are, yet, this didn't work

  • Well .... you can keep your merged cells ... if you are really cautious when building your formulas ...


    BUT ... there are two things you must correct :


    1. The format mm:ss in your reference table ...


    and


    2. Your Index Match combination ...


    Take a look at the differences between your initial formula in cell D3 ... and what I sent you as a proposal:


    Code
    =IFERROR(INDEX('Category Template'!$X$1:$X$100,MATCH(B3, 'Category Template'!$D$1:$D$100,0)),"")


    Wish you All the best for the continuation of your project ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)




  • I see, I could see the formulas differences yes, I'll give it a try for sure, but for the sake of learning, what is really the difference? Why my index.match formulas affect the conditional formatting here? I don't get it

  • Basically, it was much easier than that
    All what was needed to adjust the conditional formatting formula to be =(NUMBERVALUE(G3))>25569.0069444444
    No further modifications were needed
    And it works like magic
    Thank you for all of your assistance though

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!