Change entry from other application in date and hour

[Belleke]

Hello all,
I am looking for some code to change in column A and B.
Column A=

20171118T080000Z

Column B =

20171214T200000Z

into 4 columns like this
Column1 = 18/11/2017 (date)
Column2= 08:00 (time)
Column3 = 14/12/2017 (date)
Column4 = 20:00 (time)
Anybody an idea?
Thanks

 

[Chihiro]

Can you provide bit more sample?

Is date string always 8 character in length? I.E. 20170101 for Jan 1st?

For time, is it hhmmss?

If above is true, it's simple string manipulation and can be done via code or formula.

I'd suggest uploading sample workbook with exact location where you want the output. Should it replace the original column(s) or should it be additional columns at right of existing data?

 

[Belleke]

Hi Chihiro,
Thanks for the quick response.
Example attached.
Push the button on sheet one
(Sheet one is what i get)
Result is in sheet 2 (what I have now)
example result that I want is in sheet 3
any suggestion welcome.
Thanks.

 

[NARAYANK991]

Hi ,

See if this formula-based solution is OK.

Narayan

 

[Chihiro]

I assumed row 1 to 8 is always same and not needed.

Here's demo code to transform data brought in.

Sub Demo()
Dim myArr
Dim resArr
Dim i As Long, iRow As Long: iRow = 1
Blad5.Cells(1).CurrentRegion.Offset(1).ClearContents
With ActiveSheet
    myArr = .Range("A9:B" & .Cells(Rows.Count, "A").End(xlUp).Row).Value
    ReDim resArr(1 To Application.CountIf(.Range("A9:B" & .Cells(Rows.Count, "A").End(xlUp).Row), "BEGIN"), 1 To 8)
End With

For i = 1 To UBound(myArr)
    Select Case myArr(i, 1)
    Case "BEGIN"
        resArr(iRow, 1) = CStr(iRow & ".")
    Case "DTSTART"
        resArr(iRow, 2) = DateSerial(Left(myArr(i, 2), 4), Mid(myArr(i, 2), 5, 2), Mid(myArr(i, 2), 7, 2))
        resArr(iRow, 4) = TimeSerial(Mid(myArr(i, 2), 10, 2), Mid(myArr(i, 2), 12, 2), Mid(myArr(i, 2), 14, 2))
    Case "DTEND"
        resArr(iRow, 3) = DateSerial(Left(myArr(i, 2), 4), Mid(myArr(i, 2), 5, 2), Mid(myArr(i, 2), 7, 2))
        resArr(iRow, 5) = TimeSerial(Mid(myArr(i, 2), 10, 2), Mid(myArr(i, 2), 12, 2), Mid(myArr(i, 2), 14, 2))
    Case "DESCRIPTION"
        resArr(iRow, 6) = myArr(i, 2)
    Case "LOCATION"
        resArr(iRow, 7) = myArr(i, 2)
    Case "SUMMARY"
        resArr(iRow, 8) = myArr(i, 2)
        iRow = iRow + 1
    End Select
Next
   
    Blad5.Range("A2").Resize(iRow - 1, 8) = resArr


End Sub

 

[Belleke]

Hi Chihiro and Narayank911
Great solutions !!
Thanks

 

[Belleke]

Hi Narayank911
What is going wrong?
I must say my formula knowledge is rusty.
See attached

 

[NARAYANK991]

Hi ,

The problem is one of the data ; in some places DTSTART is followed not by DTEND but by DTSTAMP.

In quite a few places , DTSTART and DTEND are not on their own ; the actual text in these places is :

DTSTART;VALUE=DATE
DTEND;VALUE=DATE

The formula has been revised ; see the attached file.

Narayan