Hi there folks,
I need a macro (a formula wont cut it) that writes dates in the format
of "50925" (this being the 25th September 2005). I also need to append
a "-1" or a "-2" such that the first three days will appear as follows:
50925-1
50925-2
50926-1
50926-2
50927-1
50927-2
I also need write these dates for a whole year.
The idea is to have a user select one of the two "High Tides"
for any day of the year from a lookup table.

When this is done a graphical representation will be produced using the
parameters drawn from the lookup.

Hope someone can help

and Many thanks in advance.

Geoff

Here is a solution -- needs refinement but it works!


Sub DateBuild()
Dim syear As Long, smonth As Integer, sday As Integer
Dim eyear As Long, emonth As Integer, eday As Integer

' set start of period
syear = 2005
smonth = 1
sday = 1
' set end period date
eyear = 2005
emonth = 12
eday = 31


Dim sDate As Date, eDate As Date
sDate = DateSerial(syear, smonth, sday)
eDate = DateSerial(eyear, emonth, eday)

Dim x As Date, xr As Long, xstep As Integer
Dim smn As String, sdy As String
xr = 1
For x = sDate To eDate
xstep = 1
For xstep = 1 To 2

smn = Month(x)
If Month(x) < 10 Then smn = "0" & CStr(Month(x))
sdy = Day(x)
If Day(x) < 10 Then sdy = "0" & CStr(Day(x))

Cells(xr, 1) = CStr(Year(x) - 2000) & smn & sdy & "-" & CStr(xstep)

xr = xr + 1
Next xstep
xstep = xstep + 1
Next
End Sub

--
Cheers
Nigel



wrote in message
oups.com...
Hi there folks,
I need a macro (a formula wont cut it) that writes dates in the format
of "50925" (this being the 25th September 2005). I also need to append
a "-1" or a "-2" such that the first three days will appear as follows:
50925-1
50925-2
50926-1
50926-2
50927-1
50927-2
I also need write these dates for a whole year.
The idea is to have a user select one of the two "High Tides"
for any day of the year from a lookup table.

When this is done a graphical representation will be produced using the
parameters drawn from the lookup.

Hope someone can help

and Many thanks in advance.

Geoff

Thank you Nigel....

That works perfectly. Even takes care of the varying numbers of days in
a month. Probably even a leapyear as well.

Also learnt a heap about VBA.

Geoff

Hi Geoff,

a bit shorter:
For the year 2005

Sub test()
Dim lngD As Long
Dim i As Integer
range("a1").Select
For lngD = 38353 To 38353 + 364
For i = 1 To 2
ActiveCell.Value = Right(Format(lngD, "yymmdd") & "-" & i, 7)
ActiveCell.Offset(1, 0).Select
Next
Next
End Sub

For other periods please change the numbers in
"For lngD = 38353 To 38353 + 364")

e.g first half 2006 it would be
"For lngD = 38717 To 38717 + 182")

Wolf

" wrote:

Thank you Nigel....

That works perfectly. Even takes care of the varying numbers of days in
a month. Probably even a leapyear as well.

Also learnt a heap about VBA.

Geoff

Wolf.

Thankyou for that very eloquent piece of code. It also worked
perfectly.
Just loved the way you stripped that leading zero off.

Regards
Geoff