Calculating overlapping times to determine how many minutes two aircraft in one piece of airspace.

[Marksma]

Hello, I am using Excel 2010 and trying to develop a template that will calculate how many minutes two aircraft are in the same sector (piece of airspace). For example: ( I don't know how to upload the spreadsheet so you can see it.)

The first aircraft enters the sector at 13:45, the second aircraft enters at 13:59…the first aircraft then exits the sector at 14:05 which puts them in the same sector of airspace for 6 minutes.

So for columns I have Date, Sector #, Time enter sector for a/c #1, Time exit sector for a/c #1, Time enter sector for a/c #2, Time exit sector for a/c #2, Minutes aircraft in same sector together. I formatted the time column custom, hhmm and am entering time in 24 hour clock and manually entering the colon :.

A: Date B: Sector # C: a/c1 Time in D: a/c1 Time out E: a/c2 Time in F: a/c2 Time out G: Minutes together H: has this formula =IF(D5>E5,1,0) in order to solve the issue of getting a negative number if a/c 2 enters the sector after a/c1 is no longer there (so they are not in the same sector at the same time)

A	B	C	D	E	F	G =(MAX(E5,C5)-MIN(D5,F5))*H5	H =IF(D5>E5,1,0)
DATE	SEC #	TIME IN	TIME OUT	TIME IN	TIME OUT	MIN SAME SECTOR	1 OR 0
7-2-15	18	13:30	13:50	13:45	13:55	5	1
7-3-15	18	23:55	00:10	23:59	00:15	11	1
7-4-15	18	14:05	14:15	15:00	15:10	0	0
7-5-15	18	14:00	14:15	13:20	13:50	10 (erroneous)	1
7-6-15	18	15:15	16:00	15:25	15:45	-0.013888888888889	1

<tbody>
</tbody>

The top three are what it should come up with, the bottom two are examples of it going wrongly for me!

In Column G, I have this formula =(MAX(E5,C5)-MIN(D5,F5))*H5 (if it's a negative number it will be multiplied by 0 from column H and show 0 for no minutes in the same sector at the same time.

For those times that span the date (i.e. 11:55 p.m to 12:05 a.m. or 2350 to 0005). I sort of solved this by putting in 2405 instead of 0005). But am not sure it will always work, mostly because I don't understand why it converts it to 0005 in the cell but still recognizes that it's a bigger number to make the formula in column H work.

The formula =(MAX(E5,C5)-MIN(D5,F5))*H5 is working for some but not others.

I don't even know if I'm on the right track. There are so many variables. Anyone have any ideas how to figure out how many minutes two aircraft are in the same sector at the same time?

 

[DRSteele]

I think this might work. You need to put the dates and times in the columns C through F so that dealing with events wrapping around midnight is easy. So the entry 7/2/15 13:50 is actually 42187.5763888889 formatted as date with time.

Disclaimer: Don't blame me if the planes crash!




Date Sector A/C 1 in A/C 1 out A/C 2 in A/C 2 out Overlap (minutes)
7/2/2015 18 7/2/15 13:30 7/2/15 13:50 7/2/15 13:45 7/2/15 13:55 5.00

=(E2<=D2)*(F2>=C2)*(MIN(D2,F2)-MAX(E2,C2))*24*60


7/3/2015 18 7/3/15 23:55 7/4/15 0:10 7/3/15 23:59 7/4/15 0:15 11.00
7/4/2015 18 7/4/15 14:05 7/4/15 14:15 7/4/15 15:00 7/4/15 15:10 -
7/5/2015 18 7/5/15 14:00 7/5/15 14:15 7/5/15 13:20 7/5/15 13:50 -
7/6/2015 18 7/6/15 15:15 7/6/15 16:00 7/6/15 15:25 7/6/15 15:45 20.00

 

[RickXL]

Hi,

First thing, I would always use the full DateTime values for this kind of calculation. So never separate the date and the time but combine them into one cell. You can format the cells to just show the time portion but always use the full DateTime. That way times across midnight will come out correctly.

Your layout works with this formula. I used an extra MAX instead of your extra column.

Excel 2013
	A	B	C	D	E	F
1	SEC #	TIME IN	TIME OUT	TIME IN	TIME OUT	MIN SAME SECTOR
2	18	02/07/2015 13:30	02/07/2015 13:50	02/07/2015 13:45	02/07/2015 13:55	00:05
3	18	03/07/2015 23:55	04/07/2015 00:10	03/07/2015 23:59	04/07/2015 00:15	00:11
4	18	04/07/2015 14:05	04/07/2015 14:15	04/07/2015 15:00	04/07/2015 15:10	00:00
5	18	05/07/2015 14:00	05/07/2015 14:15	05/07/2015 13:20	05/07/2015 13:50	00:00
6	18	06/07/2015 15:15	06/07/2015 16:00	06/07/2015 15:25	06/07/2015 15:45	00:20
Sheet1

Cell Formulas
Range	Formula
F2	=MAX(MIN(C2,E2)-MAX(B2,D2),0)

It might be worth comparing each flight with every other one. That would give you a grid. You can format 00:00 as blanks by using semi-colons in the formulas. For example:
HH:MM;;

You could also use Conditional Formatting to highlight the shared sector times in a different colour.

Excel 2013
	A	B	C	D	E	F	G	H
1	SEC #			18	18	18	18	18
2		TIME IN		02/07/2015 13:45	03/07/2015 23:59	04/07/2015 15:00	05/07/2015 13:20	06/07/2015 15:25
3			TIME OUT	02/07/2015 13:55	04/07/2015 00:15	04/07/2015 15:10	05/07/2015 13:50	06/07/2015 15:45
4	18	02/07/2015 13:30	02/07/2015 13:50	00:05
5	18	03/07/2015 23:55	04/07/2015 00:10		00:11
6	18	04/07/2015 14:05	04/07/2015 14:15
7	18	05/07/2015 14:00	05/07/2015 14:15
8	18	06/07/2015 15:15	06/07/2015 16:00					00:20
Sheet2

Cell Formulas
Range	Formula
D4	=MAX(MIN($C4,D$3)-MAX($B4,D$2),0)
E4	=MAX(MIN($C4,E$3)-MAX($B4,E$2),0)
F4	=MAX(MIN($C4,F$3)-MAX($B4,F$2),0)
G4	=MAX(MIN($C4,G$3)-MAX($B4,G$2),0)
H4	=MAX(MIN($C4,H$3)-MAX($B4,H$2),0)

 

[Marksma]

Don't worry, these planes have already flown through so you and your formula are safe! haha

I will take a look. Thank you for your help.

 

[Marksma]

RickXL

Thanks so much, I like the different approaches to laying it out. Especially since sometimes there are THREE that we have to compare...(I'll attempt to wrap my brain around that one once I figure out two of them!)

I'm trying to understand the extra MAX.. I'm a newbie and reading/writing formulas...I get the middle part inside the parentheses, but the extra MAX and last zero are throwing me. Is it sort of "enter the MAX of either the result of the parentheses or 0, whichever is greater? Which would keep it from being a negative number?

 

[Marksma]

RickXL, It worked! Thank you so much. Hopefully I understand the use of that extra MAX and ,0 but either way, it worked like a charm.

 

[RickXL]

Good news!

The MAX works like your extra column. It is saying, if the answer is positive then go with it but if the answer is negative then set it to zero.

Regards,