Time Difference If Statement

[RayClarke]

Hi,

I am looking to calculate a time difference depending on the values in another column.
In the same way I can normally SumIF (if two values are the same) is there a way I can subtract IF?

In the example below, i want to find the difference between the 2 time stamps if the EventID is the same (i.e. how long the event lasted)
There will only ever be two identical eventID's, but they won't necessarily be one after the other on the sheet.

Line 38 All alarm list.xls
	A	B
1	Date Time	EventID
2	2021-05-12 00:54:30	{050A0E27-6095-4E71-9187-C6EFFC12C272}
3	2021-05-12 00:54:31	{6E78EF9F-9294-4E5A-AA7E-D9360C0DBE54}
4	2021-05-12 00:54:40	{6E78EF9F-9294-4E5A-AA7E-D9360C0DBE54}
5	2021-05-12 00:54:43	{050A0E27-6095-4E71-9187-C6EFFC12C272}
6	2021-05-12 02:36:26	{2D6FAD89-05BC-432E-9D66-C6ACDF596972}
7	2021-05-12 02:36:27	{25D734D3-4F75-4FFC-B83B-FEE68FC1F80B}
8	2021-05-12 02:36:40	{25D734D3-4F75-4FFC-B83B-FEE68FC1F80B}
9	2021-05-12 02:36:47	{2D6FAD89-05BC-432E-9D66-C6ACDF596972}
10	2021-05-13 09:02:14	{838879B0-B4D6-4DBF-A285-C9587135C9F9}
11	2021-05-13 09:02:14	{9CFB062C-8E6E-4397-81EE-32942E00BD22}
12	2021-05-13 09:02:24	{9CFB062C-8E6E-4397-81EE-32942E00BD22}
13	2021-05-13 09:02:27	{838879B0-B4D6-4DBF-A285-C9587135C9F9}
Sheet4

Any help would be greatly appreciated.

Thanks,
Ray

 

[Dave Patton]

try Aggregate or with 365 Max Filter Min Filter

Date and Time 2021.xlsm
	A	B	C
1	Date Time	EventID
2	12-05-21 00:54	{050A0E27-6095-4E71-9187-C6EFFC12C272}	00:00:13
10b

Cell Formulas
Range	Formula
C2	C2	=AGGREGATE(14,6,$A$2:$A$11/($B$2:$B$11=B2),1)-AGGREGATE(15,6,$A$2:$A$11/($B$2:$B$11=B2),1)

 

[steve the fish]

Heres a couple:

=MAXIFS(A:A,B:B,B2)-MINIFS(A:A,B:B,B2)
=ABS(2*(AVERAGEIFS(A:A,B:B,B2)-A2))

 

[RayClarke]

try Aggregate or with 365 Max Filter Min Filter

Date and Time 2021.xlsm
	A	B	C
1	Date Time	EventID
2	12-05-21 00:54	{050A0E27-6095-4E71-9187-C6EFFC12C272}	00:00:13
10b

Cell Formulas
Range	Formula
C2	C2	=AGGREGATE(14,6,$A$2:$A$11/($B$2:$B$11=B2),1)-AGGREGATE(15,6,$A$2:$A$11/($B$2:$B$11=B2),1)

This worked! Thank you very much. Aggregate isn't something i've used before, it's very useful.