CODEWITHSUNDEEP
excelvbadate
moontemplar212
moontemplar212

Reputation: 53

How do I split a custom DateTime format in excel using VB Macros?

I have a document with the following:

FullDateTime FullDate FullTime Day Month Year Hour Minute Second

dd/mm/yyyy hh:mm:ss AM/PM

and I would like to fill in the other columns using macros to split the first column and place the whole date, whole time, day, month, year, hour, minute and second in the other columns. FullDateTime is every five minutes and I want to the DateTime to run for a whole year. I imagine the code to look something like:

Sub Func()
    Dim 5mindays as Integer = 12*24*365
    Dim x As Integer
    Dim date
    Dim time
    For x = 1 To 5mindays
        Split(," ")
        Split(,"/")
        Split(,":")
        .Offset(0,1) = date(0)
        ...
        .Offset(0,8) = time(2)

        Add the next FullDateTime field below the existing one (adding 5 minutes)
    Next

But have no idea how to actually do it. Please give me some ideas on how to solve this. Thanks!

Upvotes: 0

Views: 77

Answers (1)

user4039065
user4039065

Reputation:

Try after setting the correct worksheet name and year to process,

Option Explicit

Sub funk()

    Dim dt As Long, yr As Long, tm As Long, dttm As Double

    yr = 2018
    dt = DateSerial(yr, 1, 1)

    With Worksheets("sheet6")
        Do While Year(dt) = yr
            Do While TimeSerial(0, tm * 5, 0) < 1
                dttm = dt + TimeSerial(0, tm * 5, 0)
                .Cells(tm + 1 + (dt - DateSerial(yr, 1, 1)) * 288, "A").Resize(1, 9) = _
                    Array(dttm, dt, dttm - dt, _
                          Day(dt), Month(dt), yr, _
                          Hour(dttm), Minute(dttm), 0)
                tm = tm + 1
            Loop
            tm = 0
            dt = dt + 1
        Loop

        With .Range(.Cells(1, "A"), .Cells(.Rows.Count, "I").End(xlUp))
            .Columns("A").NumberFormat = "dd/mm/yyyy hh:mm:ss AM/PM"
            .Columns("B").NumberFormat = "dd/mm/yyyy"
            .Columns("C").NumberFormat = "hh:mm:ss"
            .Columns("D:I").NumberFormat = "0"
        End With
    End With
End Sub

Upvotes: 1

Related Questions