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?

Date Time

Expression

Accepted answers

ChrisTX

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.

Annotation 2020-06-03 070024.png

Chris

mceleavey

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.

Solution.yxmd

All comments

ChrisTX

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

Annotation 2020-06-02 160210.png

Chris

Calculate seconds within specific interval with start and stop time - ChrisTX.yxmd

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.

Solution.yxmd

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703