J
jennifer
I am currently attempting to write code that will take
input from the user as to the start and stop date of an
event of interest, then generate a range of dates by hour
(e.g. 1/1/03 12:00 AM, then 1/1/03 1:00 AM, etc.) between
the specified dates. These will be used to vlookup y
values corresponding to the dates on another sheet. I am
having what seems to me to be a strange problem and did
not find any information on it in net searches or by
searching this group.
The following is the portion of the code relating to the
problem:
Storing the start date information from the user:
CommandButton1_Click_EnterEvent:
Dim StartDate As Date
StartDate = DateValue(InputBox("Enter event start
date:", "Event Start Date"))
Later in the code I use a do loop to generate the dates
and print them to a worksheet:
Dim Counter As Integer
Dim dt As Date
dt = StartDate + TimeValue("12:00 AM")
Counter = 8
ActiveSheet.Cells(Counter, 2) = dt
Do While dt < StopDate
dt = dt + TimeValue("01:00:00")
Counter = Counter + 1
ActiveSheet.Cells(Counter, 2) = dt
Loop
If I use a date increment of one minute (e.g. dt = dt +
DateAdd("n" , 1 , dt), this works. If I use an increment
of two hours or one day it works. But if I use an
increment of one hour (done as shown above, or with
DateAdd, or by simply saying "dt = dt + (1 / 24)," the
dates fill in successfully until 11:00 PM--but then the
next line is midnight the same day instead of midnight the
next day.
Example output in worksheet column:
5/1/2003, 5/1/2003 1:00, 5/1/2003 2:00, ... ,5/1/2003
23:00, 5/1/2003 0:00 (not 5/2/2003 0:00 as it should be).
If I check this by dragging the first few dates over to
the next column and autofilling them manually, the dates
proceed to 5/2/2003 0:00 as they should. Also, the
first "5/1/2003" does indeed represent 5/1/2003 0:00
because the dates are used in a lookup table--and the
first "5/1/2003" returns the same lookup value as the
second "5/1/2003 0:00."
I am at the end of my rope with this one. Thanks in
advance for your time and assistance.
input from the user as to the start and stop date of an
event of interest, then generate a range of dates by hour
(e.g. 1/1/03 12:00 AM, then 1/1/03 1:00 AM, etc.) between
the specified dates. These will be used to vlookup y
values corresponding to the dates on another sheet. I am
having what seems to me to be a strange problem and did
not find any information on it in net searches or by
searching this group.
The following is the portion of the code relating to the
problem:
Storing the start date information from the user:
CommandButton1_Click_EnterEvent:
Dim StartDate As Date
StartDate = DateValue(InputBox("Enter event start
date:", "Event Start Date"))
Later in the code I use a do loop to generate the dates
and print them to a worksheet:
Dim Counter As Integer
Dim dt As Date
dt = StartDate + TimeValue("12:00 AM")
Counter = 8
ActiveSheet.Cells(Counter, 2) = dt
Do While dt < StopDate
dt = dt + TimeValue("01:00:00")
Counter = Counter + 1
ActiveSheet.Cells(Counter, 2) = dt
Loop
If I use a date increment of one minute (e.g. dt = dt +
DateAdd("n" , 1 , dt), this works. If I use an increment
of two hours or one day it works. But if I use an
increment of one hour (done as shown above, or with
DateAdd, or by simply saying "dt = dt + (1 / 24)," the
dates fill in successfully until 11:00 PM--but then the
next line is midnight the same day instead of midnight the
next day.
Example output in worksheet column:
5/1/2003, 5/1/2003 1:00, 5/1/2003 2:00, ... ,5/1/2003
23:00, 5/1/2003 0:00 (not 5/2/2003 0:00 as it should be).
If I check this by dragging the first few dates over to
the next column and autofilling them manually, the dates
proceed to 5/2/2003 0:00 as they should. Also, the
first "5/1/2003" does indeed represent 5/1/2003 0:00
because the dates are used in a lookup table--and the
first "5/1/2003" returns the same lookup value as the
second "5/1/2003 0:00."
I am at the end of my rope with this one. Thanks in
advance for your time and assistance.