Home / Questions / 553473
Accepted
eMihl
Asked: 2022-08-25 07:00:56 +0800 CST 2022-08-25 07:00:56 +0800 CST

Long format of date and time in VBA - Excel

  • 772

I have a data source with a "timestamp" column that reflects the time as follows:

2022-08-17T19:20:41.218+0200

My question is if there is any direct way, that does not need a loop that goes through all the files (there are tens of thousands a day), to transform that date format into a more friendly one of the style:

2022-08-17 19:20

I have tried the following, among other things, with no positive results:

Rango.Columns("F").NumberFormat = "mm/dd/yyyy HH:mm"

Rango.Columns("F").NumberFormat = "mm/dd/yyyy hh:nn"

Thanks for the help.

date

1 Answers

  1. Best Answer

    I finally got it like this:

    For Each c In Rango.Cells

        If c.Column = 6 Then
            fecha_formatoSimple= Mid(c.Value, 1, 10) + " " + Mid(c.Value, 12, 5)
            c.Value = fecha_formatoSimple
        End If

Next

    I thought that making a loop would take a long time given the file extension, but it turns out not, it's been a few seconds.

    I use the Mid function to collect the portions of the String that interest me (date, hour and minute), store it in a String and rewrite each cell with that new format.

    • 2