Total Hours by using formulas and conditional formatting

[fgfgicare]

Dear Experts,

I am new here. I have uploaded the excel spreadsheets for you.

The questions are inside of the spreadsheet.

Hope to hear from you soon.

Thanks.

[hrlngrv]

Looks like you're asking how to convert substrings to numbers for conditional formatting.

Given your example, meaning using the range in it, select B6:K9 with B9 the active cell, then use the conditional formatting FORMULAS

For red background
=MATCH(--LEFT(B6&0,FIND("H",B6&"0H")-1),{0;350;400})=3

For yellow background
=MATCH(--LEFT(B6&0,FIND("H",B6&"0H")-1),{0;350;400})=2

[Pepe Le Mokko]

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[fgfgicare]

Hi Hrlngrv,

Thank you for your reply. I'm going to try to explain in depth.
May we first show the red cell?
As I tried to use your formula codes in the conditional formatting and choose "Use a formula to determine which cells to format" but it doesn't show cells that are more than 400H or higher.

Image attached: picture 2.PNG and picture.PNG

The aim of this is to automatically show the red cell when I turn on the car machine hours like 450H63M.
The table is going to expand while I key through.
From my first post you can use my Excel attachment to display the correct formulas.

Post to me, if you can't understand my reasoning.

[fgfgicare]

Hi Pepe,

Thank you very much for your feedback.
I'm going to try to explain all of my posts in detail in the future.

[hrlngrv]

Sorry. I screwed up in my previous response. B6 should be the active cell when you apply the conditional formatting formula.

[fgfgicare]

Hi hrlngrv,

Could you search where I'm wrong because I paste the code into the conditional formatting and it's still unloading the red cell?

I have the Excel workbook uploaded.

Thank you.

[hrlngrv]

This time I tested. Can't use array constants in conditional formatting formulas, so necessary to use a defined name. I added thresholds referring to ={0;350;400}. Then I changed the conditional formatting formulas to

For red background
=MATCH(--LEFT(B6&0,FIND("H",B6&"0H")-1),thresholds)=3

For yellow background
=MATCH(--LEFT(B6&0,FIND("H",B6&"0H")-1),thresholds)=2

[fgfgicare]

Hi hrlngrv
Thank you for the test and it works well.

I have one question that I have added to the yellow highlighted picture Attachment 666913
The first question, why is there a # REF? Was it an mistake of reference?

If there is a # REF error, what should I do to make it smooth?

Hope to hear from you.

[hrlngrv]

There are #REF! errors in defined names in your workbook attachment in #7. I have no way to correct those.

[fgfgicare]

Hi hrlngrv,

Ooops, my old brain, sorry. Ooops. Okay, now, it fits fine.

Many Thanks.