Incrementing times and dates

Hi,

I'm trying to set up 2 columns - one with a times and one with dates.
I would like to incerement each row of the time column by a few minutes and
seconds and auto increment the date shen the time goes over 12am. So it
would look like this :-

18:00:00 07/07/2005
18:03:45 07/07/2005
18:07:12 07/07/2005
.. .. .. .. .. ...
23:59:12 07/07/2005
00:02:22 08/07/2005

I tried setting up seperate integers for the hours,minutes,seconds,
day,month,year but got stuck at the first step of adding say 3 minutes 10
seconds to the next row of time.
Any help greatly appreciated

Regards

JS

IF USING YOUR HANDS:
may i suggest creating one column with date+time,
if will be easy to increment (write two first lines, select them, then
right clic the small square in the right-bottom, go down, stop on the
final date, in the menu choose to increment as a serial list; not
integer one !)

then put in two adjacent colmuns the formulas:
(example : my column in A, date in B, time in C)
in B1: =DATE(YEAR(A29);MONTH(A29);DAY(A29))
in B2: =TIME(A1)

for each one right-double-clic the right-bottom-square
the formula will propagate down to the end of list

select thoose 2 columns copy / paste-special the values
delete the column A

you have your result

IF USING A MACRO:
then you may add a constant value one to the other
(the step constant must be:
[ {(sec)/60 + min}/60 + hour ]/24 + day
)

and that's all

Thanks for the reply.
I am using all VBA for this. It working partly now but I'm getting funny
formatting.
The following is my test code :-

Sub testsheet()
' Open file
"C:\test.xls"

Dim hour As Integer
Dim minute As Integer
Dim second As Integer
Dim day As Integer
Dim month As Integer
Dim year As Integer
Dim mytime As String
Dim mydate As String
Dim daysinmonth As Integer

Dim x As Integer

Rem set up time and date

daysinmonth = 31 + 1 'add one day extra to fix modulo returning zero ie
30/30
hour = 19
minute = 54
second = 0
day = 30
month = 6
year = 2005

Windows("test.xls").Activate
For x = 1 To 200

mytime = hour & ":" & minute & ":" & second
mydate = day & "/" & month & "/" & year

Cells(x, 8).Value = Format(mytime, "hh:mm:ss") 'Rem row,column
Cells(x, 10).Value = Format(mydate, "dd/mm/yyyy")

second = second + 46
If second >= 60 Then
minute = minute + 1
second = second Mod 60
End If

minute = minute + 3
If minute >= 60 Then
hour = hour + 1
minute = minute Mod 60

End If
If hour >= 24 Then
day = day + 1
hour = hour Mod 24
End If

If day >= daysinmonth Then
month = month + 1
day = day Mod daysinmonth
End If

Next x

Rem save and close
ActiveWorkbook.Save
Workbooks(Workbooks.Count).Close

End Sub

The following is some output :-

23:41:56 30/06/2005
23:46:19 30/06/2005
23:50:42 30/06/2005
23:55:05 30/06/2005
23:59:28 30/06/2005
00:03:51 31/6/2005
00:08:14 31/6/2005
00:12:37 31/6/2005
00:17:00 31/6/2005
00:21:23 31/6/2005
00:25:46 31/6/2005
00:30:09 31/6/2005
00:34:32 31/6/2005
00:38:55 31/6/2005
0:43:18 31/6/2005
0:47:41 31/6/2005
0:52:04 31/6/2005
0:56:27 31/6/2005


What I need to know is why is the date and time formatting changing and not
consistent ?

regards

well, excel knows how to work with dates
you do not need all thoose tests (if sec > 60 ...)

=================
Sub test()
Dim i&, D As Date, TimeStep As Date, Cell As Range

Set Cell = [A1] ' first cell to write
D = "8-7-2005 00:00" ' start date
TimeStep = "03:05:00" 'date/time increment

For i = 0 To 9 ' 9 means 10 cells
Cell.Offset(i, 0).Value = D
D = D + TimeStep
Next i

End Sub
===================


then , you'll need to understand that excel will change the cell's
format when writing in an empty cell a new data. For example, if you put
a date, the date format will be selected. But if you are entering a
date+time date then the date+time format will be selected.

But if you write a date in an old cell with an other format, then the
date will not be written as wished. You may change the format of cells
before writing into them...

ok cool,

What if I want to add the date and time to a string variable - is this
possible ? ie "This cell contains 03:05:00 25/06/005" - can I do date and
time arithmetic still?

"abcd" <[email protected]> wrote in message
news:[email protected]...
> well, excel knows how to work with dates
> you do not need all thoose tests (if sec > 60 ...)
>
> =================
> Sub test()
> Dim i&, D As Date, TimeStep As Date, Cell As Range
>
> Set Cell = [A1] ' first cell to write
> D = "8-7-2005 00:00" ' start date
> TimeStep = "03:05:00" 'date/time increment
>
> For i = 0 To 9 ' 9 means 10 cells
> Cell.Offset(i, 0).Value = D
> D = D + TimeStep
> Next i
>
> End Sub
> ===================
>
>
> then , you'll need to understand that excel will change the cell's format
> when writing in an empty cell a new data. For example, if you put a date,
> the date format will be selected. But if you are entering a date+time date
> then the date+time format will be selected.
>
> But if you write a date in an old cell with an other format, then the
> date will not be written as wished. You may change the format of cells
> before writing into them...
>
>

doh - just thought - of course the dates are manipulated first and then
added to the string.
BTW I managed to finish my project using my old methods but this is useful
info.

well take a better look: these are not strings

they are converted from strings to dates
just because of the Dim declaration

D is supposed to be a date, so D = "..."
makes VBA try to convert the "..." to the date type.

But you may use D = CDate("...") if you want to much more understand the
key of my code. (this CDAte is auto-added by VBA by default)

> well take a better look: these are not strings
>
> they are converted from strings to dates
> just because of the Dim declaration
>
> D is supposed to be a date, so D = "..."
> makes VBA try to convert the "..." to the date type.
>
> But you may use D = CDate("...") if you want to much more understand the
> key of my code. (this CDAte is auto-added by VBA by default)

Many thanks,

I have improved my code by using your method - it saved me a lot of time and
stress.

Cheers

JS