Calculating the number of simultaneous incidents

[Gsc265]

Hi everyone,

I am looking to calculate from my dummy dataset over which hours of the day vehicles are deployed (between alert time and available time).

The dataset has 4 columns;
Incident number (column A) general format
Vehicle id (Column B) general format
Alert time (Column C) datetime (dd/mm/yyyy hh:mm:ss)
Available time (Column D) (dd/mm/yyyy hh:mm:ss)

I had successfully tried the below formula from a thread on another site; however I forgot to save it and i havent been able to get it to work since.

=MMULT(RANDARRAY(1,COUNT(Alert time column range),1,1,TRUE),BITAND({0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23}<=Alert time column range,{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23}>=Alert time column range))

Link to original question where above formula came from - Find number of simultaneous activites from a list of times

The above formula produced a output of columns from 0 - 23 and showed a value of 1 if the alert time - available time fell within the hour number. I have replicated this manually on the image.

For example, the 1st row of data shows the vehicle times between 01:31:17 - 01:52:56. As this time range falls within hour 1, the 1 column has a value of 1.
In the 2nd row of data however, the vehicle time spans over 3 hour periods (9-10, 10-11 and 11-12) so columns 9, 10 and 11 all receive a value of 1.

I hope this makes sense and any help would be greatly appreciated.

 

[Gsc265]

*typo above - formula should have said =MMULT(RANDARRAY(1,COUNT(Alert time column range),1,1,TRUE),BITAND({0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23}<=Available time column range,{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23}>=Alert time column range))

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	C	D			G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1					0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
2	11/03/2023 01:31	11/03/2023 01:52			0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
3	11/03/2023 09:50	11/03/2023 11:47			0	0	0	0	0	0	0	0	0	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0
Master

Cell Formulas
Range	Formula
G1:AD1	G1	=SEQUENCE(,24,0)
G2:AD3	G2	=IF((HOUR(C2:C3)<=G1:AD1)*(HOUR(D2:D3)>=G1:AD1),1,0)
Dynamic array formulas.

 

[Gsc265]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	C	D			G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1					0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
2	11/03/2023 01:31	11/03/2023 01:52			0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
3	11/03/2023 09:50	11/03/2023 11:47			0	0	0	0	0	0	0	0	0	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0
Master

Cell Formulas
Range	Formula
G1:AD1	G1	=SEQUENCE(,24,0)
G2:AD3	G2	=IF((HOUR(C2:C3)<=G1:AD1)*(HOUR(D2:D3)>=G1:AD1),1,0)
Dynamic array formulas.

Hi Fluff,
Thank you for the reply and very sorry about my delayed thanks.
I have tried the formula and it works on incidents where the alert time and available time are on the same day. However when the available time is on a future day, the row shows 0 for all values.


Example data.
Alert time - 01/04/2018 23:21:24
Available time - 02/04/2018 00:57:55

Any help again would be greatly appreciated.

 

[Fluff]

That is exactly what the formula you posted would do.

 

[Gsc265]

That is exactly what the formula you posted would do.

I have had trouble with the previous formula bringing up 0 for every cell, but will try again

 

[Fluff]

This will get you part way.

=IF((HOUR(C2:C3)<=G1:AD1)*(IF(INT(D2:D3)<=INT(C2:C3),HOUR(D2:D3),24)>=G1:AD1),1,0)

 

[Fluff]

How about

=IF(((HOUR(C2:C3)<=G1:AD1)*(IF(INT(C2:C3)<INT(D2:D3),24,HOUR(D2:D3))>=G1:AD1))+(IF(INT(C2:C3)<INT(D2:D3),HOUR(D2:D3)>=G1:AD1,)),1,0)

 

[Gsc265]

How about

=IF(((HOUR(C2:C3)<=G1:AD1)*(IF(INT(C2:C3)<INT(D2:D3),24,HOUR(D2:D3))>=G1:AD1))+(IF(INT(C2:C3)<INT(D2:D3),HOUR(D2:D3)>=G1:AD1,)),1,0)

HI Fluff,

That works great. It is putting the hours captured in the morning cells from the day after into the row before, which will do for what I require.

Many thanks,

 

[Fluff]

You're welcome & thanks for the feedback.