Interval Time Calculations

[iosiflupis]

Hello all,
I am working on a spreadsheet to determine flight windows. We are trying to make a spreadsheet calculate the time window between scheduled aircraft ARR (arrivals) & DEP (departures). I will be inputting the times in as 24-hr hours/minutes. When the aircraft is a departure we need the window to close (-) 15 minutes prior to departure time. If it is an arrival, we need the window to close 15 minutes before arrival. In either case we need the window to open back up 15 minutes after arrival or departure time. Basically, our drones need to be on the ground 15 prior to arrival & departure, and then can be back in the air 15 after departure and arrival.

I tried an IF statement {=IF(B3="DEP",C3-15,C3+15)} but all I got was -14.77 (formatted as a number).

I am attaching a screenshot of what I have so far. I do not need Column D, UAS Time.

Thank you all for your help!

 

[awoohaw]

Try this:

Book1
	A	B	C	D	E	F
1
2	Airline/Flight #	Arr/Dep	Time	UAS Time	Flight Window
3	WN-1190	DEP	05:25			05:10
4
5	WN-806	DEP	06:55			06:40
6
7	WN-2536	ARR	11:35			11:50
8
9	WN-1781	ARR	14:40			14:55
10
11	WN-1781	DEP	15:15			15:00
Sheet6

Cell Formulas
Range	Formula
F3,F11,F9,F7,F5	F3	=IF(B3="DEP",C3-(15/(24*60)),C3+(15/(24*60)))

 

[Fluff]

How about

=IF(B3="DEP",C3-time(0,15,0),C3+time(0,15,0))

 

[iosiflupis]

awoohaw & Fluff, thank you for your quick response. Last night after I got home my wife listened to the problem and then proceeded to turn my world upside down.

She pointed out that I would need a 30-minute time frame around arrivals and departures. I tried to search the web for ideas on how to accomplish this. No joy. Do either of you know how I might accomplish this?

 

[awoohaw]

just change the places where you see 15 to 30 in either of our formulas.

        =IF(B3="DEP",C3-time(0,30,0),C3+time(0,30,0))

=IF(B3="DEP",C3-(30/(24*60)),C3+(30/(24*60)))

Fluffs is much cleaner.

 

[Fluff]

What exactly do you want in col D, can you give some examples.

 

[iosiflupis]

Fluff & awoohaw,

I believe that I have it. I used the first answer that Fluff gave. What I did, shown in the picture, was add a row to all entries, giving each entry two times. Basically a stop and start time. Then I subtracted the next flight's 'first' time from the previous flight's 'last' time. The formula for the first time was changed to =(c3-time(0,15,0)) and the last time was changed to =(c3+time(0,15,0)). Got rid of the IF statement and the pointer clause in the IF statement. I checked it out and it works like a champ. Thank you both for your help in pointing out the path to follow!

 

[Fluff]

Glad you sorted it & thanks for the feedback.