+ Reply to Thread
Results 1 to 8 of 8

Incrementing times and dates

  1. #1
    John Smith
    Guest

    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





  2. #2
    abcd
    Guest

    Re: Incrementing times and dates

    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

  3. #3
    John Smith
    Guest

    Re: Incrementing times and dates

    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





  4. #4
    abcd
    Guest

    Re: Incrementing times and dates

    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...



  5. #5
    John Smith
    Guest

    Re: Incrementing times and dates

    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" <temp.nospamdc2@gmail.com> wrote in message
    news:OOTUkx5gFHA.1372@TK2MSFTNGP10.phx.gbl...
    > 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...
    >
    >




  6. #6
    John Smith
    Guest

    Re: Incrementing times and dates

    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.




  7. #7
    abcd
    Guest

    Re: Incrementing times and dates

    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)

  8. #8
    John Smith
    Guest

    Re: Incrementing times and dates

    > 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



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1