Tidal Times

[Tremblers]

I'm a yachtsman and need to mark tidal times on tidal atlases. For the
non-sailor, a tidal atlas shows on each page a geographical area with a
representation of tidal speed and direction for each hour up to 6 hrs before
or after high water at a standard port. Doing this manually is tedious. I
want to create an Excel 2000 spreadsheet that will automatically show hourly
up to 6 hrs before and 6 hrs after the time of high water for each day that I
type in. For example, if I type in a high water time of 23 Mar 08 2345
(perhaps in 2 adjacent colums), I would like automatically for the
spreadsheet to display to the left 23 Mar 08 2245, 23 Mar 2145 and so on up
to 6 hrs before high water. At the same time I would like it to display to
the right 24 Mar 08 0045 (note date change), 24 Mar 0145 etc up to 6 hrs
after high water time. I've tried to work this out but am stumped. Can
anyone suggest a way?

 

[Wigi]

Hello

I you type in a time and add 1/24 to it, you are 1 hour after that time. Add
2/24 and you add 2 hours. Analogously, subtract 1/24 and you are 1 hour
earlier.

Is this clear?

 

[Brad]

in G1, I typed in 3/23/2008 23:45
in F1, I typed in =g1-1/24
in E1, I typed in =f1-1-24
....

and in
H1 =g1+1/24
I1 = h1+1/24
....

Does that work for you???

 

[Tremblers]

eHi Brad

Thanks for your help. I tried your idea but in the columns either side of
the date/time I type in I get a decimal point and number whereas I need a new
decremented or incremented date/time. Any thoughts?

Thanks
Tremblers

 

[Tremblers]

Hi Wigi
Thanks for your help. I tried your idea but in the columns either side of
the date/time I type in I get a decimal point and number whereas I need a new
decremented or incremented date/time. Any thoughts?
Thanks
Tremblers

 

[Brad]

Sounds like a formatting problem

I never had Excel 2000, but in 2003 and 2007 - this is a custom number
format of
m/d/yyyy h:mm (make sure that you put in a space between the yyyy and h

Let me know if that is an option for you. I would think that it would be....

 

[Tremblers]

Hi Brad

Thanks for your patience. No joy I'm afraid. I typed 3/23/2008 23:45 (and
enter) into G1. I typed =g1-1/24 into F1 and got a decimal point and number
the first time (from Help, I gather this is how Excel deals with
dates/times). What I want obviously is the new time and date to appear
rather than the decimal number. I tried again and this time I got #VALUE!
which, again according to Help as I'm sure you know, means the wrong type of
argument or operand is used, or if the Formula AutoCorrect feature cannot
correct the formula.

All a mystery to me I'm afraid. Grateful for a view.

Thanks. Tremblers

 

[Brad]

Not familiar with Excel 2000 - can you do either of these two procedures
in my example:
Right click on G1 - select copy
then
Right click on H1 - select paste special - formats

That's method one

Right click on H1
Select format cells
Select number tab
Select Custom
in the open window (maybe under the Type)
enter
m/d/yyyy h:mm
That is method two

Let me know what happened....

 

[Brad]

Paste special should work - the below was taken off the internet

How to Use the Paste Special Command in Excel
By eHow Computers Editor

Rate: (3 Ratings)

Instead of copying entire cells, you can copy specified cell contents - such
as the cell format or the result of a formula, but not the formula itself -
with the Paste Special command in Microsoft Excel 2000. You can also switch
data from columns to rows and vice versa.

Pasting Only Values, Formulas, Comments, or Cell Formats
Step1Select the cell or range of cells you want to copy.
Step2Open the Edit menu and select Copy.
Step3Click the cell you want to paste the information into, or click the
upper left boundary of the cell range you want to paste the information into.
Step4Open the Edit menu and select Paste Special.
Step5Select Formula to copy only the formula of the cell.
Step6Highlight Values to copy only the outcome of the formula you copied
into the cell and not the formula itself.
Step7Select Format to copy just the format of the cell (font, alignment, and
so on).
Step8Choose Comments to copy only annotations for the cell.
Step9Click OK to accept the option you selected.

 

[Tremblers]

Hi Brad

I've cracked it! You were quite right; it was a format problem. Using
Paste Special and custom format together with your formula the spreadsheet
now works perfectly and will save much time in future. Many thanks for your
helpful advice.

Regards Tremblers.