Excel Date& Time Conversion

Question

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

Are the values text or date? What happens if you change cell format from `Date` to `General`? — ttaaoossuuuu, Nov 09 '14 at 08:54
Yes good point I forgot to mention that its these values are imported from a CSV file and the cell format is set to date during import. — sikandar bakht syed, Nov 09 '14 at 09:00
Check this question http://stackoverflow.com/questions/15664709/excel-date-formatting. Also with such a question allways mention your system language. — Vojtěch Dohnal, Nov 09 '14 at 10:45
@sikandarbakhtsyed, please refrain from essentially double posting your questions and wasting people's time accordingly http://stackoverflow.com/questions/26873271/importing-csv-into-excel — ako, Nov 19 '14 at 08:26
@Ako if you see all the thread and posting and comments I do agree that it was double posting but will add will try not to post possible duplicate. — sikandar bakht syed, Nov 19 '14 at 08:51
@sikandarbakhtsyed thanks--no reason to have more folks working on different versions of it. That loses the collaboration and duplicates effort and makes the site less useful for others. — ako, Nov 19 '14 at 15:38

ttaaoossuuuu · Answer 1 · 2014-11-09T09:50:06.160

2

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")

edited Nov 09 '14 at 09:50

answered Nov 09 '14 at 09:23

ttaaoossuuuu

7,786
3
28
58

As I have stated that am importing data from CSV so when I have tried that already but it format isnt changed. thats is why I want to have some vba macro or formula. One more thing I will add I dont want Mint or Sec, I only want Hour. like 12 AM,13 PM Thanks – sikandar bakht syed Nov 09 '14 at 09:48
Corrected my formula. – ttaaoossuuuu Nov 09 '14 at 09:50
Ok thanks now its working but I have to create another column and put the formula in it, can I use this formula on the same column and get the same result in the same column ? – sikandar bakht syed Nov 09 '14 at 10:22
now when I m importing it to access table using DoCMd, I have a column start time and its data type is Date/time, Now it is not recognizing this format. what can be done now ? – sikandar bakht syed Nov 09 '14 at 10:33
2

@sikandarbakhtsyed I believe what you try to ask here is another quite different question. – Vojtěch Dohnal Nov 09 '14 at 10:39
Vdohnal I am facing this problem as its related to it and if it different than please try to add your knowledge here. what I belive is that the format might be not recognizable to access as its mm.dd.yyyy can we put it like mm/dd/yyyy ? Thanks – sikandar bakht syed Nov 09 '14 at 10:53
1

First of all, why to import CSV first to Excel and then to Access - that does not make sense to me. Import directly from CSV to Access. And do not forget to mention your system language, how can we know if `mm.dd.yyyy` is standard datetime format in your country or not? – Vojtěch Dohnal Nov 09 '14 at 11:05
Vdhonal You are right it doesnt make sense but if the file size is large so customer provide us with csv file and it is normal than we use excel. Moreover I tried that CSV directly importing but the column header matching with table header mapping is complicated if you can help on this i will apprciate it, I am using VBA ACCESS 2010. Country is Saudi Arabia. – sikandar bakht syed Nov 09 '14 at 11:35
You must convert all your dates to `dd/MM/yyyy h:mm:ss` format then. In your file you have probably Azerbaijan date format `mm.dd.yyyy`. Assuming your Windows language is `ar-SA`. – Vojtěch Dohnal Nov 09 '14 at 11:45
@VDohnal yes this is what I am doing now, Can you give me a format in excle how to do it ? – sikandar bakht syed Nov 09 '14 at 13:39
@Taosique Can you write a formula which will convert my date from **10.18.2014 00:00:00** to this **10/16/2014 12:00:00 PM** – sikandar bakht syed Nov 11 '14 at 13:55
@VDohnal my windows language is English US. – sikandar bakht syed Nov 11 '14 at 13:57
1

Create a separate question like you should. And I will answer it. – ttaaoossuuuu Nov 12 '14 at 07:49

score 0 · Accepted Answer · edited May 23 '17 at 12:20

0

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.

edited May 23 '17 at 12:20

Community

1
1

answered Nov 09 '14 at 14:26

Vojtěch Dohnal

7,867
3
43
105

method range of object _worksheet failed 1004 I got his error ? – sikandar bakht syed Nov 11 '14 at 10:51
@sikandarbakhtsyed You probably calling the method from different Sheet. See http://stackoverflow.com/questions/17980854/vba-runtime-error-1004-application-defined-or-object-defined-error-when-selcti. Just add `Sheets("Sheet1").` before `Range`. – Vojtěch Dohnal Nov 11 '14 at 11:02
I am calling this from VBA ACCESS. – sikandar bakht syed Nov 11 '14 at 11:34
Then you must proceed somehow like this guy http://stackoverflow.com/questions/20073155/ms-access-vba-read-from-excel-and-also-update-that-excel – Vojtěch Dohnal Nov 11 '14 at 12:01
This code was supposed to run from Excel, so you must modify it slightly to run in from Access, like creating Excel.Application object, opening workbook and referencing sheets. So your reference would be somehow like this `myWorkbook.Sheets("some sheet").Range`. – Vojtěch Dohnal Nov 11 '14 at 12:09
here is My code: `Set xlApp = New Excel.Application 'Set xlWB = xlApp.Workbooks.Open("C:\Users\user\Desktop\tests.xlsx", , False) For Each WS In xlWB.Worksheets If WS.Name = "data" Then lastRow = WS.Range("A" & Rows.Count).End(xlUp).Row For Each cell In WS.Range("A" & "1:" & "A" & 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 If Next` – sikandar bakht syed Nov 11 '14 at 12:33
`For Each cell In Range(column & "1:" & column & lastRow)` this code is giving error type mismatch ? – sikandar bakht syed Nov 15 '14 at 19:42

Excel Date& Time Conversion

2 Answers2

Linked