Tide Times

P

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
699
Office Version
  1. 365
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
 
Sort by date Sort by votes
MrExcelPlayground24.xlsx
ABCDEFGHIJKLM
1DateMarina OpenMarina CloseHigh Tide 1High Tide 2Open 1Close 1Open 2Close 2Lock Open 1Lock Close 1Lock Open 2Lock Close 2
21/1/20258:0018:000:0012:0021:002:459:0014:45  9:0014:45
31/2/20258:0018:000:5112:5121:513:369:5115:36  9:5115:36
41/3/20258:0018:001:4213:4222:424:2710:4216:27  10:4216:27
51/4/20258:0018:002:3414:3423:345:1911:3417:19  11:3417:19
61/5/20258:0018:003:2515:250:256:1012:2518:10  12:2518:00
71/6/20258:0018:004:1716:171:177:0213:1719:02  13:1718:00
81/7/20258:0018:005:0817:082:087:5314:0819:53  14:0818:00
91/8/20258:0018:006:0018:003:008:4515:0020:458:008:4515:0018:00
101/9/20258:0018:006:5118:513:519:3615:5121:368:009:3615:5118:00
111/10/20258:0018:007:4219:424:4210:2716:4222:278:0010:2716:4218:00
121/11/20258:0018:008:3420:345:3411:1917:3423:198:0011:1917:3418:00
131/12/20258:0018:009:2521:256:2512:1018:250:108:0012:10  
141/13/20258:0018:0010:1722:177:1713:0219:171:028:0013:02  
151/14/20258:0018:0011:0823:088:0813:5320:081:538:0813:53  
161/15/20258:0018:0012:000:009:0014:4521:002:459:0014:45  
171/16/20258:0018:0012:510:519:5115:3621:513:369:5115:36  
181/17/20258:0018:0013:421:4210:4216:2722:424:2710:4216:27  
191/18/20258:0018:0014:342:3411:3417:1923:345:1911:3417:19  
201/19/20258:0018:0015:253:2512:2518:100:256:1012:2518:00  
211/20/20258:0018:0016:174:1713:1719:021:177:0213:1718:00  
221/21/20258:0018:0017:085:0814:0819:532:087:5314:0818:00  
231/22/20258:0018:0018:006:0015:0020:453:008:4515:0018:008:008:45
241/23/20258:0018:0018:516:5115:5121:363:519:3615:5118:008:009:36
251/24/20258:0018:0019:427:4216:4222:274:4210:2716:4218:008:0010:27
261/25/20258:0018:0020:348:3417:3423:195:3411:1917:3418:008:0011:19
271/26/20258:0018:0021:259:2518:250:106:2512:10  8:0012:10
281/27/20258:0018:0022:1710:1719:171:027:1713:02  8:0013:02
291/28/20258:0018:0023:0811:0820:081:538:0813:53  8:0813:53
301/29/20258:0018:000:0012:0021:002:459:0014:45  9:0014:45
311/30/20258:0018:000:5112:5121:513:369:5115:36  9:5115:36
321/31/20258:0018:001:4213:4222:424:2710:4216:27  10:4216:27
332/1/20258:0018:002:3414:3423:345:1911:3417:19  11:3417:19
342/2/20258:0018:003:2515:250:256:1012:2518:10  12:2518:00
352/3/20258:0018:004:1716:171:177:0213:1719:02  13:1718:00
362/4/20258:0018:005:0817:082:087:5314:0819:53  14:0818:00
372/5/20258:0018:006:0018:003:008:4515:0020:458:008:4515:0018:00
Sheet3
Cell Formulas
RangeFormula
F2:F37F2=MOD(1+D2-3/24,1)
G2:G37G2=MOD(1+D2+2.75/24,1)
H2:H37H2=MOD(1+E2-3/24,1)
I2:I37I2=MOD(1+E2+2.75/24,1)
J2:J37J2=IF(K2="","",IF(F2<C2,MAX(F2,B2),""))
K2:K37K2=IF(G2>B2,MIN(C2,G2),"")
L2:L37L2=IF(M2="","",IF(H2<C2,MAX(H2,B2),""))
M2:M37M2=IF(I2>B2,MIN(C2,I2),"")
D3:E37D3=MOD(D2+1/28,1)
A3:A37A3=A2+1
 
Upvote 0
Thanks for your quick reply James, you obviously understand tides. I have my table listed differently to yourself and I'm struggling to arrange the formulae to match your format.

Tide Day (BST)Tide Date (BST)Event TypeMonthDay StartDay EndHigh Time TimeGate Opening TimeGate Closing Time
Friday07 February 2025High Water207:0018:0000:41
Friday07 February 2025High Water207:0018:0013:27
Saturday08 February 2025High Water207:0018:0002:11
Saturday08 February 2025High Water207:0018:0015:00
Sunday09 February 2025High Water207:0018:0003:41
Sunday09 February 2025High Water207:0018:0016:21
Monday10 February 2025High Water207:0018:0004:49
Monday10 February 2025High Water207:0018:0017:18
Tuesday11 February 2025High Water207:0018:0005:40
Tuesday11 February 2025High Water207:0018:0018:04
Wednesday12 February 2025High Water207:0018:0006:22
Wednesday12 February 2025High Water207:0018:0018:43
Thursday13 February 2025High Water207:0018:0007:01
 
Upvote 0
Thanks James, I realised what I was doing wrong, worked like a charm thanks so much for your help

Cheers

Paul
 
Upvote 0
This might do the trick:
MrExcelPlayground24.xlsx
ABCDEFGHI
1DateDateEventMonthMarina OpenMarina CloseHigh Tide 1Gate Opening TimeGate Closing Time
2Friday2/7/2025High Water27:0018:000:41  
3Friday2/7/2025High Water27:0018:0013:2710:2716:12
4Saturday2/8/2025High Water27:0018:002:11  
5Saturday2/8/2025High Water27:0018:0014:5711:5717:42
6Sunday2/9/2025High Water27:0018:003:41  
7Sunday2/9/2025High Water27:0018:0016:2713:2718:00
8Monday2/10/2025High Water27:0018:005:117:007:56
9Monday2/10/2025High Water27:0018:0017:5714:5718:00
10Tuesday2/11/2025High Water27:0018:006:417:009:26
11Tuesday2/11/2025High Water27:0018:0019:2716:2718:00
12Wednesday2/12/2025High Water27:0018:008:117:0010:56
13Wednesday2/12/2025High Water27:0018:0020:5717:5718:00
14Thursday2/13/2025High Water27:0018:009:417:0012:26
15Thursday2/13/2025High Water27:0018:0022:27  
16Friday2/14/2025High Water27:0018:0011:118:1113:56
17Friday2/14/2025High Water27:0018:0023:57  
18Saturday2/15/2025High Water27:0018:0012:419:4115:26
19Saturday2/15/2025High Water27:0018:001:27  
20Sunday2/16/2025High Water27:0018:0014:1111:1116:56
21Sunday2/16/2025High Water27:0018:002:57  
22Monday2/17/2025High Water27:0018:0015:4112:4118:00
23Monday2/17/2025High Water27:0018:004:277:007:12
24Tuesday2/18/2025High Water27:0018:0017:1114:1118:00
25Tuesday2/18/2025High Water27:0018:005:577:008:42
26Wednesday2/19/2025High Water27:0018:0018:4115:4118:00
27Wednesday2/19/2025High Water27:0018:007:277:0010:12
28Thursday2/20/2025High Water27:0018:0020:1117:1118:00
29Thursday2/20/2025High Water27:0018:008:577:0011:42
30Friday2/21/2025High Water27:0018:0021:41  
31Friday2/21/2025High Water27:0018:0010:277:2713:12
32Saturday2/22/2025High Water27:0018:0023:11  
33Saturday2/22/2025High Water27:0018:0011:578:5714:42
34Sunday2/23/2025High Water27:0018:000:41  
35Sunday2/23/2025High Water27:0018:0013:2710:2716:12
36Monday2/24/2025High Water27:0018:002:11  
37Monday2/24/2025High Water27:0018:0014:5711:5717:42
38Tuesday2/25/2025High Water27:0018:003:41  
39Tuesday2/25/2025High Water27:0018:0016:2713:2718:00
40Wednesday2/26/2025High Water27:0018:005:117:007:56
41Wednesday2/26/2025High Water27:0018:0017:5714:5718:00
42Thursday2/27/2025High Water27:0018:006:417:009:26
43Thursday2/27/2025High Water27:0018:0019:2716:2718:00
44Friday2/28/2025High Water27:0018:008:117:0010:56
45Friday2/28/2025High Water27:0018:0020:5717:5718:00
46Saturday3/1/2025High Water37:0018:009:417:0012:26
47Saturday3/1/2025High Water37:0018:0022:27  
48Sunday3/2/2025High Water37:0018:0011:118:1113:56
49Sunday3/2/2025High Water37:0018:0023:57  
50Monday3/3/2025High Water37:0018:0012:419:4115:26
51Monday3/3/2025High Water37:0018:001:27  
52Tuesday3/4/2025High Water37:0018:0014:1111:1116:56
53Tuesday3/4/2025High Water37:0018:002:57  
54Wednesday3/5/2025High Water37:0018:0015:4112:4118:00
55Wednesday3/5/2025High Water37:0018:004:277:007:12
56Thursday3/6/2025High Water37:0018:0017:1114:1118:00
57Thursday3/6/2025High Water37:0018:005:577:008:42
58Friday3/7/2025High Water37:0018:0018:4115:4118:00
59Friday3/7/2025High Water37:0018:007:277:0010:12
60Saturday3/8/2025High Water37:0018:0020:1117:1118:00
61Saturday3/8/2025High Water37:0018:008:577:0011:42
62Sunday3/9/2025High Water37:0018:0021:41  
63Sunday3/9/2025High Water37:0018:0010:277:2713:12
64Monday3/10/2025High Water37:0018:0023:11  
65Monday3/10/2025High Water37:0018:0011:578:5714:42
66Tuesday3/11/2025High Water37:0018:000:41  
67Tuesday3/11/2025High Water37:0018:0013:2710:2716:12
68Wednesday3/12/2025High Water37:0018:002:11  
Sheet3
Cell Formulas
RangeFormula
A2:A68A2=B2
H2:H68H2=IF(I2="","",IF(MOD(1+G2-3/24,1)<F2,MAX(MOD(1+G2-3/24,1),E2),""))
I2:I68I2=IF(MOD(1+G2+2.75/24,1)>E2,MIN(F2,MOD(1+G2+2.75/24,1)),"")
B3,B5,B7,B9,B11,B13,B15,B17,B19,B21,B23,B25,B27,B29,B31,B33,B35,B37,B39,B41,B43,B45,B47,B49,B51,B53,B55,B57,B59,B61,B63,B65,B67B3=B2
B4,B6,B8,B10,B12,B14,B16,B18,B20,B22,B24,B26,B28,B30,B32,B34,B36,B38,B40,B42,B44,B46,B48,B50,B52,B54,B56,B58,B60,B62,B64,B66,B68B4=B2+1
G4:G68G4=MOD(G2+3/48,1)
D2:D68D2=MONTH(B2)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA help - Locking & unlocking on certain times of day
2
Replies
17
Views
737
dmt32
D
D
  • Question Question
Nested IF statements to analyze time data
Replies
6
Views
843
RobOrBob
R
D
  • Question Question
Can time frames/windows be used as a condition for any functions?
Replies
4
Views
285
dallas2699
D
B
  • Solved
Need to add two sets of minutes with custom formatting and round
Replies
1
Views
620
bh24524
B
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
278
Tara_A_Data
T

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top