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
"Sam Wilson" wrote:
> 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" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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
> > > >
|
