+ Reply to Thread
Results 1 to 9 of 9

Queries on total hour

  1. #1
    Registered User
    Join Date
    12-05-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    86

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

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

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


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    12-05-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    86

    Re: Queries on total hour

    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.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,937

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    12-05-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    86

    Re: Queries on total hour

    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.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,937

    Re: Queries on total hour

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

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

  7. #7
    Registered User
    Join Date
    12-05-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    86

    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. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,937

    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. #9
    Registered User
    Join Date
    12-05-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    86

    Re: Queries on total hour

    Dear JeteMc,

    Thanks for your explanation.

    Will learn it along with the exercises.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculating Each Hour Worked by Multiple Employees to get the total Cost of Each Hour
    By cs25001 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-06-2019, 06:55 AM
  2. [SOLVED] Counting Total Calls Per Hour
    By KoolKatelyn in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-19-2017, 09:30 AM
  3. Replies: 1
    Last Post: 06-20-2016, 02:06 PM
  4. [SOLVED] Round up a total to the next quarter hour
    By mlski4751 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-24-2016, 05:20 PM
  5. Replies: 2
    Last Post: 12-24-2015, 03:00 PM
  6. [SOLVED] Total hour calculation
    By thisara in forum Excel General
    Replies: 4
    Last Post: 09-01-2014, 02:43 AM
  7. Calculate total hour
    By linzheng in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 04-27-2013, 10:33 AM

Bookmarks

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