Subtract time within same row set (determined by another column)

MrDaniels

Hi gurus,

Hopefully you can give me a hand.

I want to subtract the time between statuses (rows) within a same set.

For the attached example:

AllocationCode	Time	status	Allocation_Duration
23	2/08/2020 6:30	1	0
23	2/08/2020 6:37	50	0.125833333
23	2/08/2020 6:56	60	0.315555556
23	2/08/2020 7:10	63	0.225277778
23	2/08/2020 7:20	67	0.166666667
23	2/08/2020 7:32	70	0.200555556
23	2/08/2020 7:44	80	0.205555555
23	2/08/2020 8:32	85	0.801944445
23	2/08/2020 8:41	90	0.151944445
23	2/08/2020 9:26	99999	0.747222222
25	2/08/2020 8:00	1	0
25	2/08/2020 9:03	50	1.056666667
25	2/08/2020 9:40	60	0.6225
25	2/08/2020 9:59	63	0.316944444
25	2/08/2020 10:14	67	0.246388889
25	2/08/2020 10:24	70	0.164166667
25	2/08/2020 11:09	80	0.7525
25	2/08/2020 11:37	85	0.470277778
25	2/08/2020 11:43	90	0.092777778
25	2/08/2020 12:57	99999	1.243333333

I need to subtract each time row within the same Allocation Code. My desire result is Allocation Duration column.

Status 1 is the start of the set, and 99999 is the end of the set. The data set will be sorted at status level and Allocation code.

Thanks in advance,

Cheers.

MrD

Example Subtract.png

wwatson

You could try something like this:

multirow date calc.yxmd