EXCEL VBA CSV Date format problem

Question

I am an Excel VBA newbie. My apologies if I am asking what seems to be an obvious question. I have a CSV text file I am opening in an Excel worksheet. One row of the file contains date information formatted as "YY-MMM', (ie: 9-FEB, or 10-MAY). When the Excel spreadsheet opens the file the date information is changed to "mm/dd/yyyy" format and reads as 02/09/2009 or 05/10/2009 where the YY-MMM is now MM/YY/2009, (ie: the 9-FEB becomes 02/09/2009, and the 10-MAY becomes 05/10/2009).

I would like to know if it is possible to reformat the field from YY-MMM to mm/01/yyyy.

I have tried to parse the date field after converting it to text with Range("B11", "IV11").NumberFormat = "text" However, then the value is a serial date and non-parsable.

I have been unsuccessfully looking for a list of the NumberFormat options.

If you can point me in a direction it will be much appreciated.

Answer 1

This assumes your 2 digit year is 2000. Once you end up with your incorrect date 9-Feb = 02/02/2009, you can convert this to the date you want with the formula: =DATE(Day(B11)+2000,Month(B11),1)

Answer 2

Just to answer a part of your question, here is the list of date formatting options (excluding time):

d = day of month, e.g. 7
dd = zero-padded day of month, e.g. 07
ddd = abbreviated day name, e.g. Thu
dddd = full day name, e.g. Thursday

pretty much the same for month...

m = month number, e.g. 7
mm = zero padded month number, e.g. 07
mmm = abbreviated month name, e.g. Jul
mmmm = full month name, e.g. July

years are simpler...

yy = 2 digit year number, e.g. 09
yyyy = 4 digit year number, e.g. 2009

you can combine them and put whatever separators you like in them

e.g.

YY-MMM, 09-FEB
DDDD-DD-MMM-YY, Wednesday-04-Feb-09
dd/mm/yyyy, 04/02/2009
mm/dd/yyyy, 02/04/2009

I've just been trying a few things out and I think your best bet is to change the format in the text file to conform to a more standard date arrangement. If you can reverse them (e.g. MMM-YY) you'll be fine, or split them into separate columns (what if when you import you define - as a separator as well as comma?). This is one case where Excel trying to be clever is a pain.

HTH

Answer 3

If you are currently using File > Open to open the CSV file then try Data > Import External Data > Import Data instead. This brings up the text import wizard which might give you more flexibility in how to import the file. Specifically, it lets you declare a column in the file as being text so that Excel does not try to parse the value

As Simon has explained, your terminology for the current date format is not correct. 9-FEB corresponds to d-mmm format and 02-19-2009 (NB deliberately changed to an unambiguous date for this example; 02-09-2009 is 9th Feb in the US but 2nd Sept in the UK) corresponds to mm-dd-yyyy

If you wanted to change the NumberFormat to text for the range starting in cell B11 and ending in cell IV11 then you would use:

Range("B11:IV11").NumberFormat = "@"

@ signifies text format and you need the : operator to indicate a contiguous range of cells. Your original example of Range("B11","IV11") actually indicates the union of cells B11 and IV11 and thus would only have affected two cells

It is unusual to have your data structured in rows rather than columns. Rather than having your dates in row 11, it would be more common to have them in column K instead so that you would use Range("K2:K65535") or just Rows("K") Most of the built-in Excel stuff like Sort and AutoFilter assume that your data is laid out in columns anyway. Row 1 traditionally contains the column names and then each subsequent row contains an individual record. You might want to look at how your CSV file is being generated to see if you can switich it to a more usable column-based structure

If you have a date like 19-FEB as text in a cell then, assuming that you always want the year part to be the current year, you can change it to the first day of the month in mm/dd/yyyy format in VBA. This example only changes one cell but you can use it as the basis for a wider solution:

Option Explicit

Sub main()

Dim strOrigValue As String

With ThisWorkbook.Worksheets("Sheet1").Range("B11")
    strOrigValue = .Value

    .NumberFormat = "mm/dd/yyyy"
    .Value = DateSerial(Year(Date), Month(strOrigValue), 1)
End With

End Sub

DateSerial creates a date serial number from the given year, month and day. Year(Date) extracts the year part from the current system date, Month(strOrigValue) takes the month part from the data that's already in the cell and we want the first day of the month