Calculate aircraft parking time

Hi All

I need help with excel to calculate automatically the time an aircraft has been parking.

Data:
Arriving Flight Data
Date Flight Nr Flight Reg Time Landed
30/11/2016 110 abc123 23:05
30/11/2016 148 fff333 20:45
01/12/2016 104 zzz909 16:40
01/12/2016 138 rrr565 17:45
01/12/2016 108 lll777 19:45

Departing flight Data
Date Flight Nr Flight Reg Time Depart
01/12/2016 101 abc123 06:45
01/12/2016 129 ffff333 07:50
01/12/2016 107 zzz909 17:35
01/12/2016 147 rrrr565 19:50
01/12/2016 109 lll777 20:50

I need excel to calculate the time on ground for each flight

Thanks for help

Hi Shg

Anyway to allow for the early flight to come back again in the afternoon, so that the early flight leaves in the morning and returns as a different flight in the afternoon and then leaves again?

Thanks

Hi shg

example maybe makes it easier

Arriving Flight Data
Date Flight Nr Flight Reg Time Landed
30/11/2016 110 abc123 23:05

Departing flight Data
Date Flight Nr Flight Reg Time Depart
01/12/2016 101 abc123 06:45

this was the original flight data. Now this aircraft comes back

Arriving flight Data
Date Flight Nr Flight Reg Time Depart
01/12/2016 110 abc123 16:40

Departing flight Data
Date Flight Nr Flight Reg Time Depart
01/12/2016 101 abc123 17:45

so, in this case the flight has come twice on the same day.

Thanks

Hi shg

I have now created a larger sample data sheet. The query relates to the Yellow and Green flights that is the same aircraft Reg, just that it is departing in the morning and then comes back later in the day and then departs again.

is there a way excel can differentiate between the two flights when calculating the parking time?

Thanks

Dear bebo021999

Thank you for the formula and explanation.

Have a good day.

Regards.

Hi bebo021999

I forget to mention this criteria

flights that land in the evening and depart in the morning are exempt from the parking, i.e. if they come after 10pm and leave before 05:59am. Only before 10pm and after 5:59am are they charged.
Example
If flight comes in at 23:05 and departs in the morning to 7:20 then
1. the parking fee is exempt i.e 0 as it is in the free zone time. lands after 10pm
2. as the flight departs at 7:20 then it is not exempt and so parking time is charged from 5:59 to 7:20

is there a way of putting this criteria in the formula?

Thanks

A bit more information please

so if the flight arrives at 21:00 and leaves as 5:00 what is the charge? 1 hr or 6 hrs?
if the flight arrives at 19:00 and leaves at 7:00 what is the charge? 12 hrs or 6 hrs?

Hi bebo021999

Thank you the reply. I will check and see if I can understand the way you have done this.

I can see that you have aligned the arrival and depart on the same line, but my data is not in this form. Is there any way to calculate as per my original data as I need other people to use this solution.

Regards

Dear kvsrinivasamurthy

Thank you for this. I will definitely try to understand the formula and double check the answer with those calculated by the supplier.

Regards

hi kvsrinivasamurthy

I have looked at your formula and for the flights that come in and go out same day, the formula is okay. However, for the evening flights and then out on the following morning the time parked is not correct.

Parking time is free from 22:00 to 05:59 (i.e. 10pm to 5:59am) only. Outside these times the flight time is charged.

Do you mind to check?

Thanks

Hi Bebp0201999

Is it possible to use the exact time ie 22:00 and 05:59 in your calculation as my calaculation needs to be more precise?

Regards

Hi kvsrinivasamurthy

is there any way I can 'force' excel to convert the data I copy paste into this excel so that all formats are correct and the values are calculated? otherwise, I will get #Value errors?

Regards

Hi kvsrinivasamurthy

Please find file attached. Here I have data that I want to work on in excel, but all the formula give errors. Is it also possible for excel cells to show blank when there is no data but become live when there is?

Regards

Hi kvsrinivasamurthy

is this the only way I can get the data copy pasted into the correct way? is there no way excel can be 'forced' to make the change automatically?

thanks

Hi All

I have some calculations that involve working out how many 15 min units are in a time. I can then charge for each 15 mins unit. Example, if the time is
1 hr 14 mins this equals 5 x 15 mins.(if the mins rollover into the next 15 mins it counts as 1 unit) so the answer should be 5. I can use the roundup function to make this 5, but if the time was 1 hr 15 mins this would be 5 exactly, but roundup gives me 6. I have attached my excel and highlighted in yellow the two cells that are incorrect. Any one know how to prevent roundup calculating the wrong answer.

Thanks

Hi kvsrinivasamurthy

Thank you very much.

Best regards.

Dear All

I have attached a sample excel sheet of data. The problem I have is that I have the aircraft departing time okay, however I need to match this to the aircraft arriving time. The formula I have for reasons unknown gives me the incorrect result and doubles the aircraft time missing the actual arriving time. I would be grateful if someone would kindly have a look and provide a solution.
Regards

Vba1234

Dear All

I have re-submitted and attached an updated excel sheet (Sample Sheet)- attached. I am trying to use the formulae that were given to me by the forum members 'bebo021999',
and 'kvsrinivasamurthy' and I would be very grateful if you could have a look to identify why the formula is not giving me the correct result. I have put the correct dates and time that should be displayed in RED colour.

Regards

Vba1234