To populate a column with a date
 

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

To populate a column with a date
 

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

To populate a column with a date
 

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

To populate a column with a date
 

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

To populate a column with a date
 

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