Hi All
I need some help with two formulae please. I've been scratching my head with this all weekend.
Let me give a quick explanation of what I'm trying to achieve:
In column E: I have what time the marina opens in the morning
In column F: I have what time the marina closes in the evening
In column G: I have the time of high tide.
What I'm trying to calculate is:
In column H, what time will the lock gate open
In column I, what time the lock gate will close
The lock gates open 3 hours before high tide and close 2.75 hours after high tide dependant on the marina being open.
There are a few anomalies though, these are around locks opening and closing when the times conflict with the marina opening and closing times.
A few examples would be:
high tide is at say 07:00 and the marina opens at 08:00, the lock would be opened 08:00 and close at 09:45
high tide is at say 16:00 the lock would open at 13:00, but the marina closing time on this day is 18:00 so the lock would close at 18:00 not 18:45
Basically the locks only open and close withing the working hours of the marina and may only be open for a short time not the full 5.75 hours.
I'm having a nightmare working this out any help would be greatly appreciate as I'm really struggling to accommodate the anomalies
Cheers
Paul
I need some help with two formulae please. I've been scratching my head with this all weekend.
Let me give a quick explanation of what I'm trying to achieve:
In column E: I have what time the marina opens in the morning
In column F: I have what time the marina closes in the evening
In column G: I have the time of high tide.
What I'm trying to calculate is:
In column H, what time will the lock gate open
In column I, what time the lock gate will close
The lock gates open 3 hours before high tide and close 2.75 hours after high tide dependant on the marina being open.
There are a few anomalies though, these are around locks opening and closing when the times conflict with the marina opening and closing times.
A few examples would be:
high tide is at say 07:00 and the marina opens at 08:00, the lock would be opened 08:00 and close at 09:45
high tide is at say 16:00 the lock would open at 13:00, but the marina closing time on this day is 18:00 so the lock would close at 18:00 not 18:45
Basically the locks only open and close withing the working hours of the marina and may only be open for a short time not the full 5.75 hours.
I'm having a nightmare working this out any help would be greatly appreciate as I'm really struggling to accommodate the anomalies
Cheers
Paul