Reputation: 323
I hope you will be fine, Can you please mention how do I convert this
10.28.2014 16:00:00
into
10/28/2014 4:00 PM.
I want to convert the whole column. Moreover if you can mention the VBA code that would be alot of help.
The mentioned excel file was created by importing csv file from the customer. My country is Saudi Arabia which has different DateTime format d/m/y
and I need also to import the processed excel file to Access database.
Thanks
Upvotes: 1
Views: 981
Reputation: 8102
Here is a code snippet in VBA that converts Azerbaijan date format m.d.y
to ar-SA date format d/m/y
. Running the FixDates
with parameter "A" should convert the text m.d.yy hh:mm:ss
to the date value d/m/yyyy hh:mm:ss
for all the cells in column A. It is using RegExp from VBScript class.
Option Explicit
Sub ConvertDates()
Call FixDates("A")
End Sub
Sub FixDates(column As String)
Dim cell As Range
Dim lastRow As Long
lastRow = Range(column & Rows.Count).End(xlUp).Row
For Each cell In Range(column & "1:" & column & lastRow)
If InStr(cell.Value, ".") <> 0 Then
cell.Value = DateValue(RegexReplace(cell.Value, _
"(\d{1,2})\.(\d{1,2})\.(\d{2,4})", "$2/$1/$3"))
End If
cell.NumberFormat = "d/m/yyyy h:mm AM/PM"
Next
End Sub
Function RegexReplace(ByVal text As String, _
ByVal replace_what As String, _
ByVal replace_with As String) As String
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = replace_what
RE.Global = True
RegexReplace = RE.Replace(text, replace_with)
End Function
If you need other format, just change one line like this:
cell.NumberFormat = "d/m/yyyy h AM/PM"
I have modified and used this answer and this answer.
Upvotes: 0
Reputation: 7894
If your cells are in Date
format, just go to Format Cells...
dialog and set custom format like this:
mm.dd.yyyy h AM/PM
If cells are in Text
format, you can use this formula to transform this text (assuming all values are exactly 19 characters long):
=LEFT(A1,11)&IF(MOD(VALUE(MID(A1,12,2)),12)=0,12,MOD(VALUE(MID(A1,12,2)),12)&IF(VALUE(MID(A1,12,2))<12," AM"," PM")
Upvotes: 2