Re: FORMULA REQUIRED (Excel 2000)

There is a strange "jump" in the start of the delay if I follow your description literally:
If the ship arrives 2 hours and 1 minute before the reference time, the delay is counted from reference time minus 2 hours.
If the ship arrives 2 hours before the reference time, the delay is counted from two hours after the arrival time, i.e. from reference time.
So 1 minute difference in arrival time means a 2 hour difference in the start of the delay.
Is this what you intend?

Formula required (Excel 2000)

Hi Gurus,

Here I go again. I am tracking the delays suffered by a vessel in port. The ship is supposed to reach the port at a certain time. This is the reference time in Column C. The actual arrival time of the ship is recorded in Column F with actual time of berthing in Col I . If the ship reaches within plus minus two hours of the reference time she is considered on time. The terminal is supposed to berth the vessel within two hours of the vessel arrival provided her arrival is within plus minus 2 hours of the reference time. The berthing delays in Col J will be Col I minus Col F. However, the berthing delay should commence two hours after the vessel reaches the port. In case the vessel reaches the port more than two hours earlier than the reference time the berthing delay will count from commence from reference time minus two hours. On all other occasions the berthing delays will commence two hours after the vessel arrival time. Now the problem:
formula required which can calculate the berthing delays in column J.
Formula required in Cell F14 which will count the number of times vessel on time.
Formula in cell F15 which will count the number of times the vessel reached port earlier than reference time minus 2 hours.