Date format not changing (VBA code)

E

EX_AX

New Member
Joined
Mar 7, 2008
Messages
6
Hi..

I have a CSV file that I import and then reformat with VBA code. The problem is that some cell's in the date column are in text format? Not sure why because if I click the cell and hit return it changes to a date? If I try to format the cells manually it's fine!!

I've tried the text to columns and this works if I do it manually, when I try VBA to do this I have the same problem.

Any ideas??
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Frequently you can set the import settings to let Excel know that these are dates.

In the Text Import Wizard this is available on Step 3, where you can specify column information. In VBA you can also do the same with the opentext method (see the help files):

Example:
Code: 
Workbooks.OpenText Filename:= _
    varFileName, Origin:= _
    437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=False, FieldInfo:=Array(Array(1, xlMDYFormat), Array(2, 1), _
    Array(3, 1)), TrailingMinusNumbers:=True

In the above, the first column is marked as a date format as a hint to excel. Often, the general (default) format works, but if it does not you may need to give excel a more direct hint.

Working with text dates won't always be a problem, however. You can also use these dates as text with a formula:

If you text date is in Cell A1:
=DateValue(A1)

In a pinch, the text dates can always be converted as needed, in Excel or in Excel VBA.

Regards.


Edit: note, if you record your import with the macro recorder, setting the column data definition as needed in step 3 of the open text import wizard, you will also have the code you need. BTW, to get the Text Import Wizard to kick in for you, change the extension of the file from .csv to .txt (google for show hidden file extensions if you aren't sure about how to do this...). Regards.
 
Last edited:
Upvote 0
I change the csv file to .txt and recorded the macro, I then copied this into my code. If you look it's still not changing the format to date for Array 9 (column's I:I?). If I do this manually it works fine..

The raw data has >1000 lines of data with column I:I being the date. Can I use a one line code to change the text to date?

Code: 
 Workbooks.OpenText Filename:=.SelectedItems(lngCount), Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 4), Array(10, 1)), TrailingMinusNumbers:=True

I was using this code to open multipul csv files;

Code: 
Dim lngCount As Long
 
       With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .Show
        
        For lngCount = 1 To .SelectedItems.Count
        
          Workbooks.Open Filename:=.SelectedItems(lngCount)
 
Upvote 0
Sorted with this code...

Code: 
Columns("I:I").Select
   Dim Cell As Object
   For Each Cell In Selection
      If IsDate(Cell.Value) Then
         Cell.Value = DateValue(Cell.Value)
      End If
    Next Cell
 
Upvote 0
Just realised this is not working, the format is still messed up from the initial data import. some dates are showing as mm/dd instead of dd/mm. It's confusing me because when I do tghs manually it's fine!!!!
 
Upvote 0
Hi,
these constants listed in help for the OpenText Method are not particularly self-explanatory.
Code: 
Sub foo()
Debug.Print xlGeneralFormat
Debug.Print xlTextFormat
Debug.Print xlMDYFormat
Debug.Print xlDMYFormat
Debug.Print xlYMDFormat
Debug.Print xlMYDFormat
Debug.Print xlDYMFormat
Debug.Print xlYDMFormat
Debug.Print xlEMDFormat
Debug.Print xlSkipColumn
End Sub
Your recording is using a value of 4 for column 9, which is xlDMYFormat. Perhaps it should be xlMDYFormat instead, or a value of 3?

However, it is rather disconcerting that you report dates showing up as mm/dd or dd/mm...You may want to import as text after all, if this is due to an error in interpreting the data during the import. Are the dates in consistent format when you import them as text, or is the problem in the data itself?

Two notes:
1) What is the problem with the dates as text anyway? Can you just leave them that way?

2) Your code to convert looks alright basically, but it is possible you may find yourself writing text dates back in as text again, so for kicks:
Code: 
Sub foo()
    For x = 1 To 10
            If IsDate(Cells(x, 1).Value) Then
                myDate = Cells(x, 1).Value
                Cells(x, 1).NumberFormat = "General"
                Cells(x, 1).Value = myDate
            End If
    Next x
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Weird Excel 2021 Date Formatting Not Working
Replies
8
Views
225
Dave Patton
D
I
  • Question
Unable to change date format
Replies
2
Views
72
Imajica
I
M
  • Question
Getting Power Query data to an Excel worksheet
Replies
4
Views
245
MichaelMorris
M
A
  • Solved
Change cell values with VBA code
Replies
1
Views
88
rpaulson
rpaulson
A
  • Question
2029 rule when extract saved as CSV
Replies
4
Views
145
Micron
M

Forum statistics

Threads
1,215,398
Messages
6,124,699
Members
449,180
Latest member
craigus51286

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top