Has anyone seen a problem where filled transactions and new balances are imported with a date in the future? All transactions/balances from all accounts (multiple institutions) are coming in with a date that is consistently 4 years and 1 day in the future. Each time I fill, I do a find/replace of all 2026 dates in the Transactions and Balance History tabs to fix the issue with the new transactions/balances, but each I time I fill again, new transactions/balances have the same issue.
I’m guessing that there is a cell somewhere that retains an incorrect 2026 date, but it must be in a hidden area of the workbook otherwise the search/replace would find it. I’ve unhidden all tabs, but still drawing a blank.
I’ve tried creating a new linked spreadsheet and that pulls in the dates correctly, so I know its something to do with my customized Excel workbook.
Any suggestions on where to look would be appreciated.
I have determined that the date formats differ between files. I had to go into the File Options, Advanced the scroll down to “When calculating this workbook” section to UNCHECK “Use 1904 date system”, OK.
Then I had to go into my sheets and look for dates that were wrong. For example, 2018 should be 2021, 2019 should be 2022. Using Find/Replace “column”, I updated that dates.
Now imported transactions and balance history line items come in with the correct dates.
Thanks Lorraine - that fixed it! Everything is importing correctly now. Just one suggestion for correcting the shifted dates once you change the date system if anyone else has this issue: Any existing dates in your workbook will be shifted back by exactly 1462 days, so the easiest way to correct them is as follows:
Enter 1462 in any empty cell and copy that cell. Then select any dates that need to be fixed and choose Paste Special, select Values under Paste and then select Add under Operation. This will correct each date precisely.