CODEWITHSUNDEEP
excelexcel-2010vba
TankTank
TankTank

Reputation: 83

Date and Time Change 24 hours with VBA Excel

How do I add time in 24 hours to this macro I have I already created? Here is the Macro below. I need to add a time stamp to it and I am not sure of how.

Private Sub Workbook_Open()

Dim dtDate As Date

dtDate = InputBox("Date", , Date)

Set SelRange = Range("B:B")
For Each b In SelRange.Rows
b.Value = dtDate
b.Offset(0, 1).Value = dtDate + 1
Next
End Sub

I need the time to increment like for 24 hours base off the macro for this worksheet. I have an example of what I need to do. In this example the date and time is showing I need the time to increment in a 24 hour period.

enter image description here

Upvotes: 1

Views: 4070

Answers (1)

K_B
K_B

Reputation: 3678

First of all I recommend to always use Option Explicit. This can be enabled for future projects through your menu in the MS VB window: Tools>Options>Editor (tab)>Require Variable Declaration (tickbox)

Now to business: If you want a range (B2:B25 and C2:C25 in this example) to be filled with date + time with 1 hour increments I recommend using something like the following:

Option Explicit

Private Sub Workbook_Open()

    Dim SelRange As Range
    Dim b As Range
    Dim dtDate As Date
    Dim intHours As Long

    'Enter the starting date and time
    dtDate = InputBox("Start date", , Date)

    'Initate our hours counter
    intHours = 0

    'Assign a worksheet range for our date-time range
    Set SelRange = Range("B2:B25")


    'Write the date-time range with 1 hour increments
    For Each b In SelRange
        b.Value2 = dtDate + TimeSerial(intHours, 0, 0)
        '24 hours later in the cell to the right of this
        b.Offset(0, 1).Value2 = dtDate + 1 + TimeSerial(intHours, 0, 0)
        intHours = intHours + 1
        'To avoid an overflow in the TimeSerial function the intHours are keps smaller than then Integer maximum
        If intHours > 24 Then
            intHours = intHours - 24
            dtDate = dtDate + 1
        End If
    Next

End Sub

As you mention you start on a date, therefore I start with a date (no time) in the InputBox.

Now if you want this to be repeated only a limited times you can do this by limiting the range it applies to (B2:B25 in this case), the loop will stop when it reaches the bottom.

It now automatically fills the C column with a date time 24 hours in the future, If you just want it to have the date in the future skip the Timeserial part in the line for the C column. If you want this to be 1 hour in the future then change the line to:

b.Offset(0, 1).Value2 = dtDate + TimeSerial(intHours +1, 0, 0)

Upvotes: 1

Related Questions