Transaction dates are incorrect after importing a bank file
When importing CSV and QIF bank files, PocketSmith tries to work out the date format that has been uploaded without asking you for additional input. Sometimes, PocketSmith will import transactions with the incorrect date format. This user guide will outline why this can happen and how to fix it.
If you can export an OFX file from your bank, we highly recommend using this file instead as there are fewer issues.
In this user guide
Incorrect dates on my CSV or QIF file
When you upload a CSV or QIF file, PocketSmith tries to work out the date format. In some instances, you may need to correct it. Date formats that contain any explicit month names e.g. ‘20 Dec 2014’ or ‘5 March 2015’ work fine.
If the date format is a strictly numerical format e.g. ‘03/05/2015’, ‘05-03-2015’ or ‘03.05.15’, PocketSmith uses a special algorithm to work out which "position" the day, month and year numbers are likely to be, such as ‘dd/mm/yyyy’ vs. ‘mm/dd/yyyy’.
Incorrect dates on bank files like these usually occur when:
- A bank file containing transactions at the start of the month and not throughout
e.g. A savings or investment account may only have transactions that occur on the second of the month, and therefore six months of transactions would start at 02/03/2014 and end at 02/09/2014. PocketSmith would assume that these were all transactions in February because the range of numbers in position 2 is greater than that of position 1.
- A bank file holding transactions for a short time frame
e.g. A CSV or QIF file that has a starting date of ‘04.01.2015’and ending at ‘04.11.2015’ could cause the date format to be mistaken. PocketSmith may reflect ‘4 January 2015 - 4 November 2015’, instead of 1 April 2015- 11 April 2015). If this happens, you need to correct it before uploading.
Fixing the dates on my bank file
- Edit the CSV or QIF file directly, preferably in a plain-text editor (e.g. Notepad on Windows, TextEdit on OS X, gedit on most Linux distributions). Change the dates to a descriptive format.
e.g. Change the dates in your bank file from ‘02/02/2014’ to ‘02 February 2014’ and ‘02/09/2014’ to ‘02 September 2014’. Upload your edited file into PocketSmith and the dates will be corrected.
- Sometimes it can be quicker to manually create individual transactions as they occur, instead of uploading an edited file.
Incorrect dates from an Excel file
If you open a CSV file in Excel, it will often pre-emptively change date formats within CSV files that are imported. European / Australian / New Zealand date format is dd/mm/yyyy - but Excel's default date format is set to the US mm/dd/yyyy. When the file is opened in Excel, it converts all possible dates into the US format but leaves those that would be wrong dates in US format as the default. This issue is more prevalent with older versions of Excel - 2007 or earlier.
For example - imagine transaction rows in a CSV, one with a date of 11th of April 2013 (11/04/2013) and the other with 13th of April 2013 (13/04/2013). When opened with US-default Excel, it would:
- Interpret the 11th of April 2013 date (11/04/2013) as the 4th of November 2013 (mm/dd/yyyy format)
- Leave the 13th of April 2013 date intact (as in US-format it would mean the 4th of the 13th month - an invalid date)
- All other transactions that occur before the 12th of the month would be incorrectly processed by Excel as mm/dd/yyyy
Then upon re-saving the CSV file to import into PocketSmith, all transactions that occurred on or before the 12th of the month would then be incorrect - the day would become the month, and date sequencing would break.
Ways to prevent Excel from changing the dates in my bank file
The best prevention is not to open your file in Excel.
- Open your file in a plain text editor instead (e.g. Notepad on Windows, TextEdit on OS X, gedit on most Linux distributions).
- Check your locale and default date settings in Excel, but this does not guarantee compatibility.
- Use a later version of Excel. We've not seen as many occurrences of this in the past couple of years, so it may be that date detection works better in newer versions of Excel.