I have a large database-type excel sheet that I use with also tracking data and using the tracked data along with some random generators to give me a result. To put it more simply, I fly flight sims on the computer - the data from the completed flights is tracked and once a flight is complete, the excel sheet randomly gives me a new destination based on criteria (the route between point A and point B exists in the database), and the aircraft to fly the route, amongst many other pieces of information, is computed based on the distance between point A and B and the aircraft capabilities and the tracked data. The part I'm now struggling with is this:
Some airports do not allow a flight to depart before 7AM. Rather than have to search if that is the case for all 11,000+ individual airports, I have it set to where if I arrive between 10PM and 515AM to give my next departure time as 645AM (allowing 15 minutes to taxi out and be cleared for takeoff at 7AM). There is also a column for "In sim time" which would be the time that I (the pilot) would need to be in the aircraft getting it boarded and set up for the departure time specified. Since there are a variety of sizes in aircraft, some only take 30 minutes for boarding, while the largest takes 1 hour 15 minutes. So currently, I have a formula that will allow X amount of time from the arrival time until the "In sim time" (30 minutes to 1 hour 15 minutes depending on arrival aircraft) and then my departure time is an additional 30 minutes to 1 hour and 15 minutes (depending on departing aircraft). The problem is that the formula for the "in sim time" is missing the part of the 645AM departure time. So currently, if I arrive at 11PM, my departure time is computed as 645AM (correct) but my "in sim time" is 1145PM (incorrect) for example. I need to be able to say that if the departure time is 645AM then "in sim time" should be 645AM (minus) time needed for that aircraft to board. But I also need in the same cell to have the formula that if the departure time is anything other than 645AM, it still needs to calculate the "in sim time" as Departure Time (minus) Boarding Time.
I have no idea if that makes any sense to anyone other than myself.
Formula for calculating in sim time (there is a different formula in a different column for Departure time calculation):
=IF(N259="","",IF(OR(O259=$AA$3,O259=$AA$4),N259+TIME(0,30,0),IF(OR(O259=$AA$5,O259=$AA$6,O259=$AA$7,O259=$AA$8),N259+TIME(0,45,0),IF(OR(O259=$AA$9,O259=$AA$10,O259=$AA$11),N259=TIME(1,0,0),IF(O259=$AA$12,N259+TIME(1,15,0))))))
N259=Previous flight arrival time; O259=Previous flight aircraft type; AA column=list of aircraft in the fleet
This formula works, EXCEPT if I arrive from the previous flight between 22:00 and 5:15, then the departure time is calculated correctly as 6:45, but my in sim time would be the actual arrival time plus the time needed to deboard the arrival aircraft. For example: Arrive at 23:00 in an aircraft that takes 45 minutes to deboard, and then departing in an aircraft that takes 1 hour to board. But since I arrived at 23:00, my departure time would be 6:45, and therefore my in sim time should be 5:45am. But it would calculate it as in sim time of 23:45 (arrival time (plus) deboarding time) with a 6:45 departure.
What I need is to add in to that formula that if the departure time is calculated to be 6:45, then give 6:45 (minus) the respective time for the departing aircraft type to board (30 minutes, 45 minutes, 1 hour, 1 hour 15 minutes), otherwise execute the formula as listed above.
I tried it as a series of IF(AND formulas for each of the 10 aircraft types just after the ="","" command, but that returns a circular reference warning and either a blank cell or 00:00 result.
F260=Departure time; O260=departure aircraft type; AA column=List of aircraft in fleet
.........."","",IF(AND(F260=TIME(6,45,0),O260=AA$3$),TIME(6,45,0)-TIME(0,30,0),.............IF(AND(F260=TIME(6,45,0),O260=AA$9$),TIME(6,45,0)-TIME(1,0,0)..........Insert formula from above here))))))))))))))))))))
Given the complexity of what my goal is and my inability to express it properly in words, I am happy to help clarify anything that may be needed.
Some airports do not allow a flight to depart before 7AM. Rather than have to search if that is the case for all 11,000+ individual airports, I have it set to where if I arrive between 10PM and 515AM to give my next departure time as 645AM (allowing 15 minutes to taxi out and be cleared for takeoff at 7AM). There is also a column for "In sim time" which would be the time that I (the pilot) would need to be in the aircraft getting it boarded and set up for the departure time specified. Since there are a variety of sizes in aircraft, some only take 30 minutes for boarding, while the largest takes 1 hour 15 minutes. So currently, I have a formula that will allow X amount of time from the arrival time until the "In sim time" (30 minutes to 1 hour 15 minutes depending on arrival aircraft) and then my departure time is an additional 30 minutes to 1 hour and 15 minutes (depending on departing aircraft). The problem is that the formula for the "in sim time" is missing the part of the 645AM departure time. So currently, if I arrive at 11PM, my departure time is computed as 645AM (correct) but my "in sim time" is 1145PM (incorrect) for example. I need to be able to say that if the departure time is 645AM then "in sim time" should be 645AM (minus) time needed for that aircraft to board. But I also need in the same cell to have the formula that if the departure time is anything other than 645AM, it still needs to calculate the "in sim time" as Departure Time (minus) Boarding Time.
I have no idea if that makes any sense to anyone other than myself.
Formula for calculating in sim time (there is a different formula in a different column for Departure time calculation):
=IF(N259="","",IF(OR(O259=$AA$3,O259=$AA$4),N259+TIME(0,30,0),IF(OR(O259=$AA$5,O259=$AA$6,O259=$AA$7,O259=$AA$8),N259+TIME(0,45,0),IF(OR(O259=$AA$9,O259=$AA$10,O259=$AA$11),N259=TIME(1,0,0),IF(O259=$AA$12,N259+TIME(1,15,0))))))
N259=Previous flight arrival time; O259=Previous flight aircraft type; AA column=list of aircraft in the fleet
This formula works, EXCEPT if I arrive from the previous flight between 22:00 and 5:15, then the departure time is calculated correctly as 6:45, but my in sim time would be the actual arrival time plus the time needed to deboard the arrival aircraft. For example: Arrive at 23:00 in an aircraft that takes 45 minutes to deboard, and then departing in an aircraft that takes 1 hour to board. But since I arrived at 23:00, my departure time would be 6:45, and therefore my in sim time should be 5:45am. But it would calculate it as in sim time of 23:45 (arrival time (plus) deboarding time) with a 6:45 departure.
What I need is to add in to that formula that if the departure time is calculated to be 6:45, then give 6:45 (minus) the respective time for the departing aircraft type to board (30 minutes, 45 minutes, 1 hour, 1 hour 15 minutes), otherwise execute the formula as listed above.
I tried it as a series of IF(AND formulas for each of the 10 aircraft types just after the ="","" command, but that returns a circular reference warning and either a blank cell or 00:00 result.
F260=Departure time; O260=departure aircraft type; AA column=List of aircraft in fleet
.........."","",IF(AND(F260=TIME(6,45,0),O260=AA$3$),TIME(6,45,0)-TIME(0,30,0),.............IF(AND(F260=TIME(6,45,0),O260=AA$9$),TIME(6,45,0)-TIME(1,0,0)..........Insert formula from above here))))))))))))))))))))
Given the complexity of what my goal is and my inability to express it properly in words, I am happy to help clarify anything that may be needed.
