Results 1 to 7 of 7

Thread: Time calculation-Event hrs and event hrs by munites

  1. #1
    Neophyte ABC111's Avatar
    Join Date
    Sep 2021
    Posts
    4
    Articles
    0
    Excel Version
    2016

    Time calculation-Event hrs and event hrs by munites



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I need help to calculate event hour and event hour by minutes base on event occurred. Base on date and time on sheeh1. we need to calculate and populate event by hours and event hour by minutes.
    Shee1: Column A has DATE;
    Column E has time on;
    Column F has time off

    Sheet 2:Currenly we enter manually in column H(Event by HE) and Column I(Event minutes per HE).
    I did try to apply formula in Column N and its working but this formula need to be update that can work for next date as per sheet 1 as well next event and event can be happened more than 1 per day. There might be possible that event can't be happened every day.

    Column N : =IF(C14<=C15,IF(HOUR(TIME(RIGHT(A14,2),0,0))=(HOUR(Sheet1!$E$7)),Sheet1!$E$7,IF(HOUR(TIME(RIGHT(A14,2),0,0))=(HOUR(Sheet1!$F$7)),Sheet1!$F$7,IF(AND(HOUR(TIME(RIGHT(A14,2),0,0))>HOUR(Sheet1!$E$7),HOUR(TIME(RIGHT(A14,2),0,0))<HOUR(Sheet1!$F$7)),TIME(RIGHT(A14,2),0,0)," "))))

    Thanks,
    Attached Files Attached Files

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    343
    Articles
    0
    Excel Version
    O365

  3. #3
    Neophyte ABC111's Avatar
    Join Date
    Sep 2021
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Sorry Alansidman.
    And thanks for pointing this rules.
    As you can see nobody response to other forum. Can some one help me here.
    Thanks again

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,001
    Articles
    0
    Excel Version
    365
    A start in the attached:
    Possible table solutions at cells G76 and M76 of Sheet2.
    Both are based on the blue table on sheet Sheet1 (copy).

    To see it in action, you need to update/add to that blue table, adjusting the vertical extent of that table as needed, then in the Data tab of the ribbon, in the Queries & Connections section, click on Refresh All to update the results tables.

    This is Power Query.
    Attached Files Attached Files

  5. #5
    Neophyte ABC111's Avatar
    Join Date
    Sep 2021
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Thanks P45cal.
    Would you please explain to me what and how you did this. I need to apply this in real sheet. CTG was an example sheet.
    Much appreciate your help.
    ABC111

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,001
    Articles
    0
    Excel Version
    365
    As mentioned in the last sentence of my last message this is Power Query, which is also known as Get & Transform Data. Since you have Excel 2016 take a look at https://www.wikihow.com/Activate-Pow...-in-Excel-2016
    You shouldn't need to 'activate' it, it's probably already there.
    Unfortunately I can't give you an explanation of 'what and how'; it would take me a lot longer than it took me to write it.
    Clicking on the Queries and Connections icon in the Queries and Connections section of the Data tab of the ribbon should bring up a pane on the right hand side showing the queries in that workbook. There are 6 at the moment but they're not all needed: zzz(2) is definitely not needed - it's developmental only. Other's may not be needed depending on which output you want (I offered 2, you may want a 3rd (which could include all the hours between your first date and last date which, in your sample data, is about a month, meaning 30 days, 24 hours per day = 720 rows of data, 92% of which would be empty)).
    To transfer this to your 'real' workbook you would need to:
    • Copy the necessary queries from my previously attached workbook to yours (you can do this with a standard copy/paste from one Queries & Connections pane to another)
    • Make a proper Excel table (Insert Table on the Insert tab) of the data in whatever sheet is the equivalent of Sheet1 in your 'real' workbook ensuring that:
      • it includes at least the 3 columns headed Date , Time On and Time Off
      • that those column headers are exactly the same as in your CTG.xlsx file that you attached in msg#1 (this includes the space you have after Date and the same upper/lower case throughout)
      • that the table covers as many rows as necessary
      • name this table Table1

    • Then you'd need to make sure the output (the results table) is where you want it; I'm not sure what happens here: it could be that the first time you refresh a query it puts the results on a new sheet - I'm not sure. It would be safer to right click the query you want to appear on a sheet (this might be Merge1 and/or zzz) and choose Load to…, then in the subsequent dialogue box choose where you want the Table to appear. If you put it in the wrong place, a standard cut and paste of the whole table to the right location is all you need to do.


    You can explore some of the steps taken in the queries by right-clicking somewhere in a result table, choosing Table, then Edit Query… which should pop up the Power Query editor. Click on some of the steps on the right to see what happens to the data.

    Note that I said this was a start, because I see that there are some more columns in your Sheet2 table but don't know how they're derived.

    If your 'real' workbook is hugely different from your example workbook it might be easier to attach a more realistic workbook here.
    Last edited by p45cal; 2021-09-21 at 02:28 PM.

  7. #7
    Neophyte ABC111's Avatar
    Join Date
    Sep 2021
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Thanks P45cal.
    I will try
    Lots of appreciation
    ABC111

Posting Permissions

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