Consulting

Results 1 to 12 of 12

Thread: spilt date and time to new columns

  1. #1
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location

    spilt date and time to new columns

    Hi
    Could you please help me with the following code to split the date and time from selection to two separate adjutant columns?
    The code is working fine but I know that there a room for improvement instead of duplicating the same code. But I'm not much sure of it using the arrays.
    Thanks, in advance.

    Sub DateTime()
    Dim myVals As Variant, y As Variant
    Dim i As Long
    
    
    myVals = Selection.Value2
    On Error Resume Next
    For i = 1 To UBound(myVals)
        y = Split(myVals(i, 1))
        myVals(i, 1) = Format(y(0), "dd/mmm/yyyy")
    Next i
    ActiveCell.EntireColumn.Offset(0, 1).Insert
    Selection.Offset(, 1).Value = myVals
    Erase myVals
    
    
    myVals = Selection.Value2
    On Error Resume Next
    For i = 1 To UBound(myVals)
        y = Split(myVals(i, 1))
        myVals(i, 1) = Format(y(1), "hh:mm AM/PM")
    Next i
    ActiveCell.EntireColumn.Offset(0, 2).Insert
    Selection.Offset(, 2).Value = myVals
    
    
    End Sub

  2. #2
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    618
    Location
    INT.jpg
    Attached Files Attached Files

  3. #3
    Hi! this works for me, how do u like it?

    Sub DateTime2()
    Dim myVals As Variant
    Dim i As Long
    Dim Datestr As String, Timestr As String
    Columns(ActiveCell.Column + 1).Resize(, 2).Insert
     '2 inserted columns' format-> string
    Selection.Offset(0, 1).Resize(, 2).NumberFormatLocal = "@" 'need to change this @ to a normal one(I changed this due to post error)
    myVals = Selection.Value2
    For i = 1 To UBound(myVals)
        Datestr = Format(myVals(i, 1), "dd/mmm/yyyy")
        Timestr = Format(myVals(i, 1), "hh:mm AM/PM")
        Selection.Item(i).Offset(0, 1) = Datestr   'the selection has to be 1 column
        Selection.Item(i).Offset(0, 2) = Timestr   'the selection has to be 1 column
    Next i
    End Sub

  4. #4
    you can just copy the cells to new cells (two cells for date and time).
    Change the Format of the first cell to Date and the other as Time.

  5. #5
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Thanks Logit for the formula. but I was looking for the vba code to be part of the sub

  6. #6
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Thanks, this works

  7. #7
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    But this will change only the display format
    I was looking for a code to be part of the sub

  8. #8
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    618
    Location
    Sub SplitLongDate()Dim D As Range, i As Long
    Dim H As Integer, M As Integer, S As Integer
    
    
        For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
            Set D = Range("A" & i)
            If D = "" Then Exit Sub
            D.Offset(0, 1).Resize(1, 2) = Split(D, " ")
            H = Hour(D): M = Minute(D): S = Second(D)
            D.Offset(0, 2) = H & ":" & M & ":" & S
            D.Offset(0, 2).NumberFormat = "hh:mm:ss"
        Next i
        
    End Sub

  9. #9
    in reality, date/time has Always the Date portion. if you strip the date portion, still it has the date on it (even day 1).
    Logit has code, but see, he is also formatting it to appear as if there is No Date part.

  10. #10
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Thanks Logit
    But the array formula in my original post works faster than the looping of each cell.
    I was actually looking for a guidance to simplify the code instead of duplicating the code the way i have written
    Last edited by anish.ms; 02-29-2024 at 02:20 PM.

  11. #11
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    618
    Location
    Ok

  12. #12
    snb
    Guest
    Sub M_snb()
       selection.TextToColumns , , , , 0, 0, 0, 1
    End Sub

Posting Permissions

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