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

Quote from sktneer

You can also make a new rule for conditional formatting using the formula given below...

Code

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

I tried this, no, it didn't work

Quote from Carim

صباح الخير

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.

Quote from sktneer

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?

Quote from Carim

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

Quote from Carim

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 ...

Display More

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