Fixing Exported Dates

If you export a Profit and Loss statement from Quickbooks to a CSV file, you get dates that look like this:

Excel turns them into this.

January 2005 becomes January 5th of the current year. February 2005 becomes February 5th. That’s no good. In a spare row, I used this formula

=DATE(DAY(A1)+2000,MONTH(A1)+1,0)

to create the date I wanted. Then I copied and pasted special – values over the top of the original dates. It adds 2000 the day to get the correct year. Adding 1 to the month and taking the zeroth day will get you the last day of the month.

January 5, 2012 becomes January 31, 2005.

7 thoughts on “Fixing Exported Dates

  1. Hmm, good idea. When you change a report in QB, you have to save it as a Memorized Report or the changes don’t persist. You can’t change the built-in reports, afaik. However, maybe there’s a global use-four-digit-year setting that would set it everywhere. I believe I’ll check for that on Monday. Thanks.

  2. Excellent tip Dick. It’s going to be very helpful.
    It would be awesome if you let us know if you find more info about the 4-digit years!

  3. There is a setting in QB. Edit – Preferences – General – Company Preferences – Always show years as 4 digits (1999). Mine was already checked, so it doesn’t respect that setting. The report in question is Profit & Loss with the Columns dropdown set to Month. I didn’t see anything in the report customization dialog that sets the date format for column headings. Just another reason to scorn Quickbooks, I guess.

  4. Before importing the CSV file, you could glue “20” in front of the dates. That is, search the first line for “MMM YY” and replace with “MMM YYYY”.

    Dim intFreeFile As Integer
    Dim i As Long, str As String, arr() As String
    Dim strCSV As String

    intFreeFile = FreeFile
    Open strFileName For Input As #intFreeFile
    strCSV = Input$(LOF(intFreeFile), intFreeFile)
    Close #intFreeFile

    arr() = Split(strCSV, vbLf, 2)
    For i = 1 To 12
    str = Format(DateSerial(1, i, 1), "Mmm")
    arr(0) = Replace(arr(0), str & " ", str & " 20")
    Next
    strCSV = Join(arr(), vbLf)

    intFreeFile = FreeFile
    Open strFileName For Output As #intFreeFile
    Print #intFreeFile, strCSV
    Close #intFreeFile

  5. Picky and tangential: Split won’t handle commas within quoted fields correctly. Not an issue here.

    You don’t need the month names. In fact, you could just replace all ASCII spaces (decimal char code 32) with HTML nonbreaking spaces (decimal char code 160). Excel leaves fields with nonbreaking spaces as text. Also, unclear anything should be done with records. Safer to edit only the first line in the CSV file.

    Sub foo()
    Dim fh As Long, s As String

    fh = FreeFile
    Open “test.csv” For Input As #fh
    Line Input #fh, s
    Close #fh

    s = Replace(s, ” “, Chr(160))

    Open “test.csv” For Append As #fh
    Seek #fh, 1 ‘yes, Seek does work with Append mode
    Print #fh, s;
    Close #fh

    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *