excel cell date format

Question

This is my first question here, so I hope everything is correct. I've hacked a bit of code to read from excel and output a CSV with speech marks for every cell, blank or not. The problem is with the dates where excel converts a YYYY-MM-DD date to MM-DD-YYYY when it writes it to the CSV. To get around this I've written the following if statement:

If IsDate(Cells(r, c)) Then

Unfortunately the program now interprets a cell with "2A" in it as a date as well (even though I've checked the format in excel is text), exporting this as "1899-12-30". I've put in a very quick and dirty clause to filter this

If Selection.Cells(r, c) = "2A" Then

But I was wondering if there was a more elegant way, to convert date formats that are only in the YYYY-MM-DD or similar format. And why it thinks A2 is a date!? Full code is below:

For r = 1 To Selection.Rows.Count
    s = """"
    c = 1
    For c = 1 To Selection.Columns.Count
            If IsDate(Selection.Cells(r, c)) Then
                'ridiculous clause to get rid of A2 being treated as a date
                If Selection.Cells(r, c) = "2A" Then
                    s = s & Selection.Cells(r, c)
                Else
                    s = s & Format(Selection.Cells(r, c), "YYYY-MM-DD")
                End If
            Else
                s = s & Selection.Cells(r, c)
            End If
            If c = Selection.Columns.Count Then
                s = s & """" 'end of row
            Else
                s = s & """,""" 'mid row
            End If
    Next c
    a.writeline s
Next r

Answer 1

The problem arises because 2A is interpreted as 2 AM:

?isdate("2A")
True

?cdate("2A")
02:00:00 

Dealing with this depends on what types of values are in the column alongside the dates, you could check their length or check for the default date that's used when a DateTime contains no date part;

if format$(cdate("2A"), "YYYY-MM-DD") = "1899-12-30" then ..dodgy..

or look at its pattern; if isdate(x) and x like "####-##-##" or you could convert the value to a date and check that it falls within an appropriate range.

You could also reformat the column itself Selection.NumberFormat = "yyyy-mm-dd".

Answer 2

Separate to the good explanation from Alex as to your date issue you might want to look at my code from http://www.experts-exchange.com/A_3509.html (the full article has explanations) for optimising your overall approach as

1. Variant arrays are much more efficient than ranges
2. It handles "," insides cels, wheras your current code will treat this as a sign to split cell contents
3. When concatening a long string with two short strings it is best to join the two short strings together first (using paretheses), else the long string is joined twice

The sample below does transpose columns and rows which presumbaly you dont want to follow

Sub CreateCSV_FSO()
    Dim objFSO
    Dim objTF
    Dim ws As Worksheet
    Dim lRow As Long
    Dim lCol As Long
    Dim strTmp As String
    Dim lFnum As Long

    Set objFSO = CreateObject("scripting.filesystemobject")
    Set objTF = objFSO.createtextfile(sFilePath, True, False)

    For Each ws In ActiveWorkbook.Worksheets
        'test that sheet has been used
        Set rng1 = ws.UsedRange
        If Not rng1 Is Nothing Then
            'only multi-cell ranges can be written to a 2D array
            If rng1.Cells.Count > 1 Then
                X = ws.UsedRange.Value2
                'The code TRANSPOSES COLUMNS AND ROWS by writing strings column by column
                For lCol = 1 To UBound(X, 2)
                    'write initial value outside the loop
                    strTmp = IIf(InStr(X(1, lCol), strDelim) > 0, """" & X(1, lCol) & """", X(1, lCol))
                    For lRow = 2 To UBound(X, 1)
                        'concatenate long string & (short string with short string)
                        strTmp = strTmp & (strDelim & IIf(InStr(X(lRow, lCol), strDelim) > 0, """" & X(lRow, lCol) & """", X(lRow, lCol)))
                    Next lRow
                    'write each line to CSV
                    objTF.writeline strTmp
                Next lCol
            Else
                objTF.writeline IIf(InStr(ws.UsedRange.Value, strDelim) > 0, """" & ws.UsedRange.Value & """", ws.UsedRange.Value)
            End If
        End If
    Next ws

    objTF.Close
    Set objFSO = Nothing
    MsgBox "Done!", vbOKOnly

End Sub

Answer 3

A much simpler approach would be to use the text value in the cell e.g.

For r = 1 To Selection.Rows.Count
s = """"
c = 1
For c = 1 To Selection.Columns.Count
s = s & Selection.Cells(r, c).Text

        If c = Selection.Columns.Count Then
            s = s & """" 'end of row
        Else
            s = s & """,""" 'mid row
        End If
Next c
a.writeline s
Next r

Using .Text avoids all the issues you have encountered with formats

(I would also recommend reading this help on writing loops with Excel ranges. It means you can define rows, columns in relation to your table and not the absolute addresses or a selection)

Answer 4

This will be useful for date format

Range("U2:U" & newrow).NumberFormat = "mm/dd/yy"
Range("V2:V" & newrow).NumberFormat = "mm/dd/yy"

U and V are column and newrow is a variable