# The Naked Scientists Forum

### Author Topic: Is there an Excel formula for calculating tide times?  (Read 24687 times)

#### Graeme Scott

• First timers
• Posts: 1
##### Is there an Excel formula for calculating tide times?
« on: 18/09/2008 10:42:18 »
Graeme Scott  asked the Naked Scientists:

Hi Chris,

Is there a formula that I could use on Excel which given my
Lat / Long and other known values (date etc) would calculate the max and min tides for a particular location on a selected date?

Regards

Graeme

What do you think?

#### Bored chemist

• Neilep Level Member
• Posts: 8600
• Thanked: 41 times
##### Is there an Excel formula for calculating tide times?
« Reply #1 on: 18/09/2008 19:40:55 »
I doubt it; the effect of local currents would make it practically impossible.

#### lyner

• Guest
##### Is there an Excel formula for calculating tide times?
« Reply #2 on: 18/09/2008 22:59:37 »
There is a 'relatively simple' formula which involves a number of harmonic coefficients. But these coefficents are are found by measurement.
There are a number of open source programs for which you can see the code but you need to supply your own data from numerous observations if you want to make predictions for a new location.

#### Graeme Scott

• First timers
• Posts: 1
##### Is there an Excel formula for calculating tide times?
« Reply #3 on: 20/09/2008 08:48:52 »
Hi

Let me try and clarify my question:

Available information: Lat / Long, Date, Range of previous High and Low tide heights on particular dates in the past.

Required information: Standalone method of calculating tide height for a particular date and time in the future same location.

A real bonus would be the ability to extrapolate the tide height at a particular location on a chosen day if I had historical data for points either side of the unknown location.

Regards

Graeme

#### lyner

• Guest
##### Is there an Excel formula for calculating tide times?
« Reply #4 on: 21/09/2008 23:31:35 »
the answer is that you need a lot of data. This has to cover months of detailed tide guage output. This is then analysed into harmonic components. The resulting components are then put into a relatively simple formula to give the tidal height at any time.
It's a lot of work. There are a lot of free progs  which will do it for you at standard ports and you then time shift to your particular secondary location.
I repeat: it is not a trivial exercise. Use someone else's free effort.

#### techmind

• Hero Member
• Posts: 934
• Un-obfuscated
##### Is there an Excel formula for calculating tide times?
« Reply #5 on: 22/09/2008 19:56:16 »
Available information: Lat / Long, Date, Range of previous High and Low tide heights on particular dates in the past.

Required information: Standalone method of calculating tide height for a particular date and time in the future same location.

A real bonus would be the ability to extrapolate the tide height at a particular location on a chosen day if I had historical data for points either side of the unknown location.

Tide-height is determined principally by the combined gravitational effects of the sun and the moon, and the rotation of the earth. The moon makes a complete revolution in 28 days, so while the sun "apparently" passes over once every 24 hours, the moon gains (loses?) a day every 28. The "beat" between the primary influences gives the 28-day (with a strong 14-day component too - for the same reason we have two tides/day) cyclical change in amplitude between "spring" and "neap" tides. The seasonal change in the angle of the sun leads to strongest tides (around Britain at least) near the equinoxes, and weaker tides towards mid-summer and mid-winter.

Besides these basic drives, the coastal waters around the UK are hugely modified by water-flows along the Channel and through the Straits of Dover, or between Ireland and Wales. For this reason, a purely idealised mathematical answer (assuming no land on the planet, for example) derived from latitude and longitude alone won't get you very far.

You would need at least one-year's data for the location in order to have a reasonable stab at the problem. To iron out the effects of local weather (atmospheric pressure changes, high winds etc) data from several years would help.

You would do a Fourier analysis on the data for the main periodic components I've mentioned, and the empirical analysis should pull out most of the local effects.
The data is not exactly periodic within a year, so you need to be a bit careful with the analysis.

I did try doing such an analysis in the past (17+ years ago!), based on typing in data from a handful of printed Tide-Tables. Unfortunately the tide-tables only gave the time and height of high- and low-water; nothing in-between. Also this was many years ago when I didn't know enough about Fourier analysis to do a proper job on the data anyway. Just plotting the data alone was quite interesting though.

I will still have the data in an electronic form somewhere, though strictly it is of course HMSO copyright!

The second part of the question "real bonus" is highly dependent on the local coastline and tidal flows. In the middle of the ocean it'd be trivial, but for anywhere on the coast of Britain, you'd need quite a bit more information to do this. The HMSO publishes tidal charts for shipping which will give you a bit of an insight into what you're asking.
« Last Edit: 23/09/2008 19:18:20 by techmind »

#### lyner

• Guest
##### Is there an Excel formula for calculating tide times?
« Reply #6 on: 23/09/2008 18:09:15 »
I had a simple prog for my Palm (rubbish machine) and for my ipod touch (heaven).
They will tell you enough to plan which day to make your trip but, for secondary ports and much much more reliable info - use the almanack or Easytide, which gives you a seven day forecast for free (with graphs and all).

#### The Naked Scientists Forum

##### Is there an Excel formula for calculating tide times?
« Reply #6 on: 23/09/2008 18:09:15 »