Afternoon, I am trying to work out from a data set how long a particular type of work has been running. I have start and end times, and can create a key field, but my problem is the data is being produced over multiple years, 24 hours a day. So looking for how to group I am struggling. At the change of any item in Orange, I need to group by and subtract the min start time from max end time. However, this data is repeated several times within the table and crosses days, so I cannot add a date into a key. If there is a gap I need to count as separate. Trying to do this in powerquery.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Grouping issues
- Thread starter gplhl
- Start date
Excel Facts
Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
GlennUK
Well-known Member
- Joined
- Jul 8, 2002
- Messages
- 11,871
- Office Version
- 2019
- 2013
- Platform
- Windows
Just add subtotals for change in Key - choose MAX for Start Time and End Time, then you need to change SUBTOTAL(4 to SUBTOTAL(5 for Start TIme - then collapse to level 2, and create your Run Time formulas in the visible cells.
Upvote
0
alansidman
Well-known Member
- Joined
- Feb 26, 2007
- Messages
- 8,149
- Office Version
- 365
- Platform
- Windows
Reload your data using XL2BB so that we don't have to retype your data to test our solutions. This is possible in Power Query, but you need to give us data we can manipulate.
Upvote
0
Similar threads
- Question
- Replies
- 0
- Views
- 272
- Question
- Replies
- 0
- Views
- 192
- Solved
- Replies
- 4
- Views
- 937
