Calculate seconds within specific interval with start and stop time

[EstherHesseling]

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?

[ChrisTX]

Try the Generate Rows tool.  See if the attached workflow helps.

Chris

[mceleavey]

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.

[deviseetharaman]

Hi @EstherHesseling 

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

[EstherHesseling]

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?

[EstherHesseling]

Hi @deviseetharaman,

Yes indeed, this is exactly what I am trying to do!

[ChrisTX · Accepted solution]

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

[EstherHesseling]

Hi @ChrisTX 

I will try this, thanks!

I will let you know.

[mceleavey · Accepted solution]

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.

[EstherHesseling]

@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!!