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)
[TABLE="width: 800"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G =(MAX(E5,C5)-MIN(D5,F5))*H5[/TD]
[TD]H =IF(D5>E5,1,0)[/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]SEC #[/TD]
[TD]TIME IN[/TD]
[TD]TIME OUT[/TD]
[TD]TIME IN[/TD]
[TD]TIME OUT[/TD]
[TD]MIN SAME SECTOR[/TD]
[TD]1 OR 0[/TD]
[/TR]
[TR]
[TD]7-2-15[/TD]
[TD]18[/TD]
[TD]13:30[/TD]
[TD]13:50[/TD]
[TD]13:45[/TD]
[TD]13:55[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7-3-15[/TD]
[TD]18[/TD]
[TD]23:55[/TD]
[TD]00:10[/TD]
[TD]23:59[/TD]
[TD]00:15[/TD]
[TD]11[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7-4-15[/TD]
[TD]18[/TD]
[TD]14:05[/TD]
[TD]14:15[/TD]
[TD]15:00[/TD]
[TD]15:10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7-5-15[/TD]
[TD]18[/TD]
[TD]14:00[/TD]
[TD]14:15[/TD]
[TD]13:20[/TD]
[TD]13:50[/TD]
[TD]10 (erroneous)[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7-6-15[/TD]
[TD]18[/TD]
[TD]15:15[/TD]
[TD]16:00[/TD]
[TD]15:25[/TD]
[TD]15:45[/TD]
[TD]-0.013888888888889[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
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?
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)
[TABLE="width: 800"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G =(MAX(E5,C5)-MIN(D5,F5))*H5[/TD]
[TD]H =IF(D5>E5,1,0)[/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]SEC #[/TD]
[TD]TIME IN[/TD]
[TD]TIME OUT[/TD]
[TD]TIME IN[/TD]
[TD]TIME OUT[/TD]
[TD]MIN SAME SECTOR[/TD]
[TD]1 OR 0[/TD]
[/TR]
[TR]
[TD]7-2-15[/TD]
[TD]18[/TD]
[TD]13:30[/TD]
[TD]13:50[/TD]
[TD]13:45[/TD]
[TD]13:55[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7-3-15[/TD]
[TD]18[/TD]
[TD]23:55[/TD]
[TD]00:10[/TD]
[TD]23:59[/TD]
[TD]00:15[/TD]
[TD]11[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7-4-15[/TD]
[TD]18[/TD]
[TD]14:05[/TD]
[TD]14:15[/TD]
[TD]15:00[/TD]
[TD]15:10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7-5-15[/TD]
[TD]18[/TD]
[TD]14:00[/TD]
[TD]14:15[/TD]
[TD]13:20[/TD]
[TD]13:50[/TD]
[TD]10 (erroneous)[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7-6-15[/TD]
[TD]18[/TD]
[TD]15:15[/TD]
[TD]16:00[/TD]
[TD]15:25[/TD]
[TD]15:45[/TD]
[TD]-0.013888888888889[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
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?