Queries on total hour

[fgfgicare]

Dear Expert,

I have uploaded the workbook with questions and results for your attention.

Kindly, let me know if there are any queries.

Thanks.

[AliGW]

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, 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.

[fgfgicare]

Hi Ali,

OK, thanks for your clarification.

I have 2 questions from the workbook attached.

The workbook that I have uploaded is about the car tested for hours. So, here are the questions as below:-

1) At the Pressure tab, could you check for me on the function that related to the Masterlist?
2) At the Masterlist tab, when a customer care key in the hours, it will highlight in red color when it reached to 400 hours and yellow color that is more than 350. What is the function that I can do to alert the customer care?

Hope the 2 questions clarifies all, if you have any doubt, you may ask me.

Thanks.

[JeteMc]

As for Total Hours on the Pressure sheet (cell B7) try: =IFERROR(LEFT(Masterlist!B9,SEARCH("H",Masterlist!B9)-1) + 0, 0)
As to highlighting the cells on the Masterlist sheet:
Use the following formulas as the first two rules (in the order listed):
For Red: =AND(B$8="Total hour",LEFT(B9,3)+0>=400)
For Yellow: =AND(B$8="Total hour",LEFT(B9,3)+0>=350)
Let us know if you have any questions.

[fgfgicare]

Dear JeteMc,

Thanks for your solutions.

Could you advise me on the Total Hours on the Pressure sheet (cell B7) try: =IFERROR(LEFT(Masterlist!B9,SEARCH("H",Masterlist!B9)-1) + 0, 0) as I need the hours and minutes?

The conditional formatting of your formulas are working good in here.

Hope to hear from you soon.

Thank you.

[JeteMc]

Try the following:

Please Login or Register  to view this content.

Custom format the cells using: [h]:mm;@
Let us know if you have any questions.

[fgfgicare]

Dear JeteMc,
That is an outstanding formula!.
Would you mind sharing with me how do you work this out:-
=SUM(IFERROR(LEFT(Masterlist!B9,SEARCH(“H”,Masterlist!B9)-1) + 0, 0)/24,IFERROR(MID(Masterlist!B9,SEARCH(“H”,Masterlist!B9)+1,SEARCH(“M”,Masterlist!B9)-1-SEARCH(“H”,Masterlist!B9))/1440,0)
Questions:
1) Why need the -1 and +1 after the Masterlist B9?
2) I understand divide 24 is in hours and what about divide by 1440, 0)?
3) how do you normally use in MID function?
Hope to hear from you soon.
Thank you very much

[JeteMc]

1. Search returns the placement of the "H" so in the case of -1, one needs to be subtracted to tell the LEFT function how many characters to include.
In the case of +1, one needs to be added to tell the MID function which character with which to start.
2. Divide by 1440 because there are 1440 minutes in a day (24*60)
3. You normally use the MID function to extract or parse characters from the middle of a text string.
You're Welcome and thank you for the feedback. If that answers your questions, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

[fgfgicare]

Dear JeteMc,

Thanks for your explanation.

Will learn it along with the exercises.