i need to change my date/time format from german to English(UK) version. I have difficulty doing so. I want to change it so that it looks like 9/12/20 09:28:00 am instead
converting text with date and time format
-
ollielly -
February 1, 2021 at 1:19 PM -
Thread is marked as Resolved.
-
-
-
Hello,
Looks like your fields are not Dates (i.e. Numbers ...) but just Text ...
Do we agree ?
-
Ya, its text thats why im facing trouble to convert it to a string to change date/time
-
Attached is your test file
Hope this will help
-
i want it in vba though and the year was wrong
-
-
i want it in vba though and the year was wrong
You are welcome
-
-
Once you have tested the macro ... feel free to share your comments
-
HI Carim,
Your macro was returning 1920 for the year, not sure how to fix it formula wise but the cDate solves the problem once you feed it a format it recognises. I modified your code as follows:
CodeSub DateFixerV3() Dim MyStr As String Dim r As Range For Each r In Range("A2:A4") MyStr = Replace(r.Text, ".", "/", 1) r.Offset(0, 6) = CDate(MyStr) Next r End Sub
HTH
Justin
-
Hello Justin,
Given the interest shown by OP about his own question ... seems there is no need to dig any further ...
-
-
I agree, but the thread exists and hopefully someone else might obtain benefit from it
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!