Build DateSeries (?)

[sgl]

Hi All,

I have the following range of dates/times

Start Date 1 Jan 08 12:00 hrs
End Date 21 Nov 08 09:30 hrs
Increment by 15 days

Need to develop a VBA routine that builds a two column series as follows
Col A ColB
StartDate StartDate + Increment
StartDate + Increment StartDate + Increment + Increment
etc
to EndDate

Calculating the total time between dates in each row should give you a total
time between SatrtDate and EndDate of 324.8958333 Days. As validation I will
need to calcualte the total time between dates in each Row
Can anyone assist me in this please!
Many thanks/sgl

 

[Sam Wilson]

Hi,

I don't think you really need any VBA for this if it's just a one-off. Why
don't you just put "01/01/08 12:00:00" in cell A2, and then put the formula
'=A2+15' in A3 and drag it down as far as you want etc?

Sam

 

[sgl]

Hi Sam,

Thanks for your quick response. I need to develop a VBA code as this is part
of a much larger project that needs substantial automation for the users.
Thanks anyway./sgl

 

[Sam Wilson]

Ok, try this:


Sub demo()

Dim d, e As Date
d = "01/01/08 12:00:00"
e = "21/11/08 09:30:00"

Dim i As Integer

With Range("a1")
Do Until d > DateAdd("d", 15, e)
.Offset(i, 0).Value = d
.Offset(i, 1).Value = DateAdd("d", 15, d)
d = DateAdd("d", 15, d)
i = i + 1
Loop
End With

End Sub

 

[sgl]

Sam Hi,
Thanks for your assistance which helped move forward. I tinkered a little
with your suggestion and have come up with the following.

Sub demo()

Dim d As Date, e As Date
d = "01/01/08 12:00"
e = "21/11/08 09:30"

Dim i As Integer

With Range("a1")
Do Until d > DateAdd("d", 0, e) 'this ensures you do not overun the
dates of 21/11/08
.Offset(i, 0).Value = d
If DateAdd("d", 15, d) > e Then
.Offset(i, 1).Value = e
Else
.Offset(i, 1) = DateAdd("d", 15, d)
End If
d = DateAdd("d", 15, d)
i = i + 1
Loop
End With

End Sub

This ensures that the last date (e) stops on 21 Nov 08 09:30 hrs which is
what I wanted to get to. Thanks for introducing me to the DateAdd VBA
function. Need to refresh myself on the VBA functions!

Thanks again/sgl