Hi all!
I have certain data (see three example rows below) with a column "start(time)" and a column "stop(time)".
I would like to calculate the seconds within every hour between 08:00:00 and 24:00:00 (or 00:00:00).
I know that I am working very inefficient on this workflow as I am using many (many many formula tools.
The first formula I use:
The second formula I use
The third formula:
The fourth formula:
Because I need to do this for all of the other hours, this is not really a nice way.
Furthermore, I don't know how to incorporate the stop time.
Could you please support me with this?
Solved! Go to Solution.
Hi @EstherHesseling ,
I've built the workflow as attached. This will calculate all seconds between those times.
You just need to append text fields as follows:
Then you can build the formula as below:
This gives the following results:
Hope this helps.
M.
Is this what you are looking for:
Start_New3 | End_New3 | 7-8 | 8-9 | 9-10 | 10-11 | 11-12 | 12-13 | 13-14 | 14-15 | 15-16 | 16-17 | 17-18 |
08:04:46 | 13:38:53 | 0 | 3314 | 3600 | 3600 | 3600 | 3600 | 2333 | 0 | 0 | 0 | 0 |
14:58:18 | 17:35:54 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 102 | 3600 | 3600 | 2154 |
07:47:19 | 15:42:18 | 761 | 3600 | 3600 | 3600 | 3600 | 3600 | 3600 | 360 | 2538 | 0 | 0 |
hi @mceleavey
I have tried the formula and it is almost right. I still need to incorporate the stop time, as it still counts 3600 seconds when the stop time has passed.
So if the stop time is 15:35:21, then it still counts 3600 seconds between 16:00:00 and 17:00:00.
Could you help me with this?
Take the workflow I posted above and add a formula to determine the Hour, then a Summarize tool.. Then you'll just need to work on the headers.
Chris
HI @EstherHesseling ,
apologies for the delay, the day job gets in the way a bit!
I've updated the formulae and attached the workflow. This should work for all calculations now.
M.
@ChrisTX Thank you for the solution.
I used your solution for a part of the workflow, for the rows with startday and stopday not being equal (for instance Friday respectively Saturday). As it generated a lot of rows and I had a total of 400K, it was really helpful for a part of the data!
@mceleavey This formula worked out, thanks a lot.
I used this solution for the other part of the workflow, for the rows with an equal start and stopday. This kept my workflow running relatively fast.
I am really happy I got the result I needed, thank you!!