Queries on total hour

1. Queries on total hour

Dear Expert,

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

Kindly, let me know if there are any queries.

Thanks.

2. Re: Queries on total hour

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.

3. Re: Queries on total hour

Hi Ali,

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.

4. Re: Queries on total hour

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.

5. Re: Queries on total hour

Dear JeteMc,

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.

6. Re: Queries on total hour

Try the following:
Formula:
`Please Login or Register  to view this content.`

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

7. Re: Queries on total hour

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

8. Re: Queries on total hour

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.

9. Re: Queries on total hour

Dear JeteMc,

Will learn it along with the exercises.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1