Difference between dates based on follow on work

[Johnstone200]

Hello,
I am looking to preform a report on calculating the time difference between one work order closing, to the time it takes for the next work order to be raised based on the asset number. The conditions need to be a PCM closing to a CM being reported. For example I wish to calculate the difference between asset number 30619361, PCM finishing on the 28/12/2022 02:27:23, to the same asset CM being reported on the 04/07/2023 07:44.
Obviously I could do this Individually for each asset, but if there is a formula that can calculate for all the data so i can filter and pick out what is required that would be great.

Hope there is help out there.

Thanks

PCM query.xlsx
	A	B	C	D	E	F	G
1	assetnum	persongroup	wonum	worktype	reportdate	actstart	actfinish
2	30619361	SPEC-D	WO-4898986	PCM	24/12/2022 09:51	28/12/2022 02:27	28/12/2022 02:27
3	30627005	SPEC	WO-4916354	PCM	07/01/2023 12:44	08/01/2023 09:00	08/01/2023 10:00
4	30619362	SPEC	WO-4917206	PCM	08/01/2023 11:10	08/01/2023 07:00	08/01/2023 08:00
5	30618897	SPEC	WO-4917231	PCM	08/01/2023 12:00	08/01/2023 13:30	08/01/2023 14:30
6	30619304	SPEC	WO-4917235	PCM	08/01/2023 12:02	18/01/2023 14:58	18/01/2023 14:58
7	30619294	SPEC	WO-4928321	PCM	17/01/2023 11:23	18/02/2023 10:00	18/02/2023 11:00
8	30627014	SPEC	WO-4935937	PCM	23/01/2023 10:54	23/01/2023 10:00	23/01/2023 11:00
9	30619100	SPEC	WO-4936041	PCM	23/01/2023 12:53	25/01/2023 12:30	25/01/2023 14:30
10	30627017	SPEC	WO-4983733	PCM	28/02/2023 23:24	06/03/2023 08:00	06/03/2023 09:00
11	30628847	SPEC	WO-5053149	PCM	26/04/2023 14:33	30/04/2023 14:00	02/06/2023 15:00
12	30645547	SPEC	WO-5056130	PCM	28/04/2023 12:06	27/06/2023 04:31	27/06/2023 04:31
13	30626989	SPEC	WO-5056157	PCM	28/04/2023 12:48	29/04/2023 08:30	29/04/2023 11:00
14	30619346	SPEC	WO-5057447	PCM	29/04/2023 14:15	03/05/2023 16:00	03/05/2023 17:00
15	30618898	SPEC	WO-5067748	PCM	07/05/2023 03:12	13/05/2023 12:05	13/05/2023 12:05
16	30628860	SPEC	WO-5077466	PCM	15/05/2023 10:54	17/05/2023 11:30	17/05/2023 12:30
17	30619072	SPEC	WO-5085005	PCM	21/05/2023 11:43	02/06/2023 14:30	02/06/2023 16:00
18	30618792	SPEC	WO-5086122	PCM	22/05/2023 14:22	23/05/2023 12:00	23/05/2023 13:45
19	30644583	SPEC	WO-5086123	PCM	22/05/2023 14:23	23/05/2023 12:00	23/05/2023 15:00
20	30619083	SPEC	WO-5092924	CM	28/05/2023 06:27	28/05/2023 07:00	28/05/2023 08:00
21	30626985	SPEC	WO-5092933	CM	28/05/2023 06:47	28/05/2023 10:00	28/05/2023 10:30
22	30626991	SPEC-D	WO-5095155	PCM	30/05/2023 11:25	22/06/2023 02:59	22/06/2023 02:59
23	30619352	SPEC	WO-5097659	PCM	31/05/2023 10:47	31/05/2023 11:30	31/05/2023 13:30
24	30619299	SPEC	WO-5097730	PCM	31/05/2023 12:49	01/06/2023 14:00	01/06/2023 16:00
25	30619313	SPEC	WO-5098489	CM	01/06/2023 02:30	01/06/2023 05:00	01/06/2023 05:30
26	30628882	SPEC	WO-5098501	CM	01/06/2023 04:04	01/06/2023 05:15	01/06/2023 06:00
27	30626985	SPEC	WO-5098524	CM	01/06/2023 06:01	01/06/2023 07:00	01/06/2023 08:00
28	30621730	SPEC	WO-5098526	CM	01/06/2023 06:12	01/06/2023 09:30	01/06/2023 10:30
29	30621730	SPEC	WO-5098681	PCM	01/06/2023 11:43	02/06/2023 14:00	02/06/2023 14:30
30	30631207	SPEC	WO-5098731	CM	01/06/2023 13:03	01/06/2023 14:30	01/06/2023 15:30
31	30619321	SPEC	WO-5098785	CM	01/06/2023 14:37	01/06/2023 16:30	01/06/2023 17:00
32	30628849	SPEC	WO-5098848	CM	01/06/2023 16:24	01/06/2023 16:30	01/06/2023 17:30
33	30628851	SPEC	WO-5099668	CM	02/06/2023 03:11	02/06/2023 05:00	02/06/2023 08:00
34	30619330	SPEC	WO-5099683	CM	02/06/2023 04:50	02/06/2023 05:30	02/06/2023 05:55
35	30628849	SPEC	WO-5099682	CM	02/06/2023 04:51	02/06/2023 05:30	02/06/2023 06:00
36	30619318	SPEC	WO-5099689	CM	02/06/2023 05:41	02/06/2023 05:55	02/06/2023 11:00
37	30626985	SPEC	WO-5099690	CM	02/06/2023 05:53	02/06/2023 08:00	02/06/2023 09:00
38	30626989	SPEC	WO-5099803	CM	02/06/2023 08:32	02/06/2023 10:00	02/06/2023 10:30
39	30628837	SPEC	WO-5099811	CM	02/06/2023 08:41	02/06/2023 10:00	02/06/2023 11:00
40	30631207	TSO	WO-5099859	PCM	02/06/2023 10:08	05/06/2023 11:48	05/06/2023 12:48
41	30626989	SPEC	WO-5099896	CM	02/06/2023 10:58	02/06/2023 11:30	02/06/2023 12:00
42	30619318	SPEC	WO-5099900	CM	02/06/2023 11:03	06/06/2023 08:00	06/06/2023 08:45
Sheet1

 

[duggie33]

Hi Johnstone200,

I do not see the second record (04/07/2023 07:44) that you mention in the supplied information. Could you update the information you provided?

Thanks,

Doug

 

[Johnstone200]

Ah, I see Doug. I have accidentally deleted that row to upload the data. In essence it is the difference between finish date of a PCM and the next reported date of a CM based on the condition that it is the same asset number. The following CM's after this will be not counted up until the next PCM to CM data. See asset 30628847 and 30626989 for an example. I am determining how long an asset is in service for after a PCM has taken place so the actual data I have covers thousands of work orders.
Hope that makes sense.

 

[Alex Blakenburg]

I would be inclined to use a helper column.
You can combine it into a single formula if you want to.
You didn't say if you wanted the result in days or hours so I have left it in days.

20231007 Elapsed Time.xlsx
	A	B	C	D	E	F	G	H	I
1	assetnum	persongroup	wonum	worktype	reportdate	actstart	actfinish	Next Start Date	Elapsed Time (days)
2	30619361	SPEC-D	WO-4898986	PCM	24/12/2022 09:51	28/12/2022 02:27	28/12/2022 02:27	Latest Date
3	30627005	SPEC	WO-4916354	PCM	07/01/2023 12:44	08/01/2023 09:00	08/01/2023 10:00	Latest Date
4	30619362	SPEC	WO-4917206	PCM	08/01/2023 11:10	08/01/2023 07:00	08/01/2023 08:00	Latest Date
5	30618897	SPEC	WO-4917231	PCM	08/01/2023 12:00	08/01/2023 13:30	08/01/2023 14:30	Latest Date
6	30619304	SPEC	WO-4917235	PCM	08/01/2023 12:02	18/01/2023 14:58	18/01/2023 14:58	Latest Date
7	30619294	SPEC	WO-4928321	PCM	17/01/2023 11:23	18/02/2023 10:00	18/02/2023 11:00	Latest Date
8	30627014	SPEC	WO-4935937	PCM	23/01/2023 10:54	23/01/2023 10:00	23/01/2023 11:00	Latest Date
9	30619100	SPEC	WO-4936041	PCM	23/01/2023 12:53	25/01/2023 12:30	25/01/2023 14:30	Latest Date
10	30627017	SPEC	WO-4983733	PCM	28/02/2023 23:24	06/03/2023 08:00	06/03/2023 09:00	Latest Date
11	30628847	SPEC	WO-5053149	PCM	26/04/2023 14:33	30/04/2023 14:00	02/06/2023 15:00	Latest Date
12	30645547	SPEC	WO-5056130	PCM	28/04/2023 12:06	27/06/2023 04:31	27/06/2023 04:31	Latest Date
13	30626989	SPEC	WO-5056157	PCM	28/04/2023 12:48	29/04/2023 08:30	29/04/2023 11:00	02/06/2023 11:30	34.0208333
14	30619346	SPEC	WO-5057447	PCM	29/04/2023 14:15	03/05/2023 16:00	03/05/2023 17:00	Latest Date
15	30618898	SPEC	WO-5067748	PCM	07/05/2023 03:12	13/05/2023 12:05	13/05/2023 12:05	Latest Date
16	30628860	SPEC	WO-5077466	PCM	15/05/2023 10:54	17/05/2023 11:30	17/05/2023 12:30	Latest Date
17	30619072	SPEC	WO-5085005	PCM	21/05/2023 11:43	02/06/2023 14:30	02/06/2023 16:00	Latest Date
18	30618792	SPEC	WO-5086122	PCM	22/05/2023 14:22	23/05/2023 12:00	23/05/2023 13:45	Latest Date
19	30644583	SPEC	WO-5086123	PCM	22/05/2023 14:23	23/05/2023 12:00	23/05/2023 15:00	Latest Date
20	30619083	SPEC	WO-5092924	CM	28/05/2023 06:27	28/05/2023 07:00	28/05/2023 08:00	Latest Date
21	30626985	SPEC	WO-5092933	CM	28/05/2023 06:47	28/05/2023 10:00	28/05/2023 10:30	01/06/2023 07:00	3.85416667
22	30626991	SPEC-D	WO-5095155	PCM	30/05/2023 11:25	22/06/2023 02:59	22/06/2023 02:59	Latest Date
23	30619352	SPEC	WO-5097659	PCM	31/05/2023 10:47	31/05/2023 11:30	31/05/2023 13:30	Latest Date
24	30619299	SPEC	WO-5097730	PCM	31/05/2023 12:49	01/06/2023 14:00	01/06/2023 16:00	Latest Date
25	30619313	SPEC	WO-5098489	CM	01/06/2023 02:30	01/06/2023 05:00	01/06/2023 05:30	Latest Date
26	30628882	SPEC	WO-5098501	CM	01/06/2023 04:04	01/06/2023 05:15	01/06/2023 06:00	Latest Date
27	30626985	SPEC	WO-5098524	CM	01/06/2023 06:01	01/06/2023 07:00	01/06/2023 08:00	02/06/2023 08:00	1
28	30621730	SPEC	WO-5098526	CM	01/06/2023 06:12	01/06/2023 09:30	01/06/2023 10:30	02/06/2023 14:00	1.14583333
29	30621730	SPEC	WO-5098681	PCM	01/06/2023 11:43	02/06/2023 14:00	02/06/2023 14:30	Latest Date
30	30631207	SPEC	WO-5098731	CM	01/06/2023 13:03	01/06/2023 14:30	01/06/2023 15:30	05/06/2023 11:48	3.84583333
31	30619321	SPEC	WO-5098785	CM	01/06/2023 14:37	01/06/2023 16:30	01/06/2023 17:00	Latest Date
32	30628849	SPEC	WO-5098848	CM	01/06/2023 16:24	01/06/2023 16:30	01/06/2023 17:30	02/06/2023 05:30	0.5
33	30628851	SPEC	WO-5099668	CM	02/06/2023 03:11	02/06/2023 05:00	02/06/2023 08:00	Latest Date
34	30619330	SPEC	WO-5099683	CM	02/06/2023 04:50	02/06/2023 05:30	02/06/2023 05:55	Latest Date
35	30628849	SPEC	WO-5099682	CM	02/06/2023 04:51	02/06/2023 05:30	02/06/2023 06:00	Latest Date
36	30619318	SPEC	WO-5099689	CM	02/06/2023 05:41	02/06/2023 05:55	02/06/2023 11:00	06/06/2023 08:00	3.875
37	30626985	SPEC	WO-5099690	CM	02/06/2023 05:53	02/06/2023 08:00	02/06/2023 09:00	Latest Date
38	30626989	SPEC	WO-5099896	CM	02/06/2023 10:58	02/06/2023 11:30	02/06/2023 12:00	Latest Date
39	30628837	SPEC	WO-5099811	CM	02/06/2023 08:41	02/06/2023 10:00	02/06/2023 11:00	Latest Date
40	30631207	TSO	WO-5099859	PCM	02/06/2023 10:08	05/06/2023 11:48	05/06/2023 12:48	Latest Date
41	30626989	SPEC	WO-5099896	CM	02/06/2023 10:58	02/06/2023 11:30	02/06/2023 12:00	Latest Date
42	30619318	SPEC	WO-5099900	CM	02/06/2023 11:03	06/06/2023 08:00	06/06/2023 08:45	Latest Date
Sheet1

Cell Formulas
Range	Formula
H2:H42	H2	=IFERROR(MIN(FILTER($F$2:$F$42,($A$2:$A$42=A2)*($F$2:$F$42>G2))),"Latest Date")
I2:I42	I2	=IF(ISNUMBER(H2),H2-G2,"")

 

[Johnstone200]

Fantastic. Thanks Alex, this works really well.

 

[Alex Blakenburg]

Appreciate the feedback. Glad we could help.