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

[wwatson · Accepted solution]

You could try something like this:

[MrDaniels]

Thanks wwatson, 

Very clean and easy solution.!!!

Brilliant

🙂