CODEWITHSUNDEEP
excelvba
sikandar bakht syed
sikandar bakht syed

Reputation: 323

Excel Date& Time Conversion

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

Answers (2)

Vojtěch Dohnal
Vojtěch Dohnal

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

ttaaoossuu
ttaaoossuu

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

Related Questions