VBA date format

[vbanewbie68]

Hi

Can anyone help me on this please?

Please see my screenshot. How to change this: 2021-11-09T15:00:57 UTC into dd/mm/yyyyy format in VBA?

Thank you in advance

Best

V

 

[Dan_W]

If those are cells C2 to C6, the following should work in VBA:

Sub ConvertCells()
    Dim Cll As Range
    For Each Cll In Range("C2:C6")
        Cll.Value = Format(Split(Cll.Value,"T")(0),"dd/mm/yyyy")
    Next
End Sub

 

[vbanewbie68]

Hi

Thank you for this but I got an error message says Run-time error '6' Overflow. Please see screenshot above.

Best regards

V

 

[Dan_W]

Noted. How do you plan on debugging and solving the problem?

 

[vbanewbie68]

Noted. How do you plan on debugging and solving the problem?

I am not follow what you mean? I am new to VBA so still learning.

 

[Dan_W]

Then here is an opportunity for you to learn.

Because it works for me:

 

[Dan_W]

So the question is - what is it about your setup that is causing the error? What is the name of the sheet that you have this data on?

 

[vbanewbie68]

There is a debug as see the screenshot above so what cause this line in relation to an error: overflow?

 

[vbanewbie68]

Got it now and it works just fine but on "c1" not sure why it has underscore instead of dash / ?

 

[vbanewbie68]

I am pleased to tell you that I have done it and it works perfectly!
So many thanks for your kind help on this matter which is much appreciated!
Best regards

V