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


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.