Time calculation-Event hrs and event hrs by munites

ABC111

New member
Joined
Sep 15, 2021
Messages
14
Reaction score
2
Points
3
Excel Version(s)
2016
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,
 

Attachments

  • CTG.xlsx
    21.7 KB · Views: 11
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
 
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.
 

Attachments

  • ExcelGuru11305CTG.xlsx
    46.8 KB · Views: 10
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
 
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-Power-Query-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:
Thanks P45cal.
I will try
Lots of appreciation
ABC111
 
Hello P45cal,
I learn some power query basic. Still I need to understand this merge step. would you help me to understand - how did you compare this time duration. (My real data sheet is too large as well much more column. Which I can't share here. So I am trying to do what you done)
Thanks in advance,
 
I need to understand this merge step. would you help me to understand - how did you compare this time duration. (My real data sheet is too large as well much more column. Which I can't share here.
1. If you PM me here, I'll give you an email address and you can send me a link to the larger (and sensitive?) file that you'll put on a sharing site - password protect access to the file on the site (not the workbook itself) if you want and send me the password privately.
2. Not promising quick results.
3. I really do not have time to tutor you - I can give a few pointers as to what to look at, but that's it.
 
Back
Top