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.
Cool piece of Excel trickery about using the zero to get last day of the month.
Cheers,
Bob
You can’t format Quickbooks to use 4-digit years?
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.
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!
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.
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 IntegerDim 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
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