Calculate Extra Charges Over Fixed Charge

[vba1234]

Dear All

I am to trying calculate the extra charges based on the following criteria. I have attached the excel I am working on. If anyone can help me I would be very grateful.

Scenarios:
1. Handling arriving time is not greater than the agreed time : 1:30. Also, there are no extra charges as no extra equipment required. Charge is Fixed charge only.
2. Handling arriving time is not greater than the agreed time : 1:30. Now, extra equipment is supplied. Total charge= Fixed Charge + Extra Charges for Equipment
3. Handling arriving time is greater than the agreed time : 1:30. Also, no extra equipment is required. Total Charge = Fixed + Extra Charges for Time
4. Handling arriving time is greater then the agreed time : 1:30. Now extra equipment is supplied. Total Charge = Fixed Charge + Extra Charges for Time and Equipment

These will need to be applied for the departure of the flight.


In the excel sheet, cell AC5 should show the extra charges. - Arriving Flight
The same for the departure should show in cell AC6

Cell N5 to AB5
I have shown the standard equipment supplied as per the Type of aircraft. If you click on the cell L5, you will see a drop down list of the various types. These then show the standard equipment as per Type in cells AG5 to AU5
In Cells AX5 to BL5 these show that rates as per equipment selected in cell N5 to AR5.

I hope is clear.

Thank you

Desh

[Sean Thomas]

Not really sure what it is you are asking to be solved?
what is not working?
what exactly do you want to be solved?

[vba1234]

Hi Sean
If you see my excel sheet, I need to charge for the extra equipment. The problem is that when the extra equipment is used it needs to be charged per hour*quantity. If the arrival time in handling is below 1:30 then the time spent handling equals zero. When the time is above the 1:30 it gets rounded to the nearest hour. On the sheet, I have given an example, but the arrival flight shows no charge. I still need the excel to calculate the charge even though it is within the 1:30

if you look in cell AC5 the answer is zero. It should calculate as in line AC6. The reason is because when the time is calculated, cell J5 is zero. I need to calculate even if zero. My other problem is that when the time in J5 is two or more then this needs to be taken into account as a multiplier?
Hope you can help.

Thanks Desh

[Sean Thomas]

Hi Desh
sorry I still don't quite understand this yet

arrival
under 1:30 so standard £550 charge for level 4
Tug dollies, Towing and portable Scale charged extra

departure
over 1hr over the allotted 1:30 (don't understand why you have used 2hrs)
so standard charge
Tug dollies, Towing and portable Scale charged extra at rate x 1hr
then additional labour - how are you calculating this?

[vba1234]

Hi Sean

Thank you for taking the time to have a look.

When the time is over 1:30 the time is rounded up to the next hour. So if the time taken for the arrival is 2:00, this is 30mins over which equates to 1hr charge, as the charge is per hour not part off. Also, the departure time is extra time in this case, so whatever the time is, it rounded off to the nearest hour. Any extra equipment is the charged per arrival and departure.

I have been working on this so I have attached my updated excel. I hope this would help.

I will need excel to calculate the extra charges when the time is zero, 1hr, and any other time above 1hr.i have entered some made up data to work on, hope it can help? IS there any way I can have excel calculate a negative time (under the 1:30) and show zero in the time, but still show any extra charges for the equipment?

Thank you and appreciate your help.

Desh