Downloaded QIF data from bank to Excel then Quicken imported date as 1920 rather than 2020

TownieManufacturing

Hello all. I'm pretty new to Quicken. We downloaded 2020 information from our bank onto an Excel spreadsheet to upload it to our Quicken ledger. For reasons unknown, year in our Quicken register is seen as 1920 rather than 2020. Any ideas as to how this might have happened? Any quick fix recommendations? Should we blow away all the 1920 data and reupload the report from our bank? Thanks for any ideas you may have.

TownieManufacturing

FYI we downloaded the data from our bank in QIF format.

GeoffG

Is this the only option the bank offers? This is not the best way to load current Quicken data.

TownieManufacturing

Hi GeoffG. What way do you recommend? Thank you.

GeoffG

This depends on the bank's offering. The best method is using Direct Connect. This article explains the various options that Quicken uses.

Connection Types in Quicken

Chris_QPW

For what's it worth whatever generated the QIF file used a 2 digit year format, without Quicken's special syntax for years after the year 2000.

Dates need to be either:
3/29/2021
OR
3/29'21

Ps56k2

TownieManufacturing said:  We downloaded 2020 information from our bank onto an Excel spreadsheet to upload it to our Quicken ledger.

A couple of points....
What Release of Quicken....   Help --> About Quicken

What bank are you downloading ?
Why are you downloading QIF and using Excel - vs just Quicken downloads ?

[EDIT] - also updated topic title to better reflect the actual Q&A

UKR

The Y2K bug still rears its ugly head

I can only guess, but it looks like the QIF import file is using an incorrect Date format for your transactions. Have you ever heard of the Y2K bug? That's what's going on here, in your system, as it is configured right now ...

If all the dates in the import file are formatted MM/DD/YY with a 2-digit year, then the date is interpreted as MM/DD/19YY

The dates would have to be formatted as

• either MM/DD'YY with a 2-digit year and a single quote before the year, to be interpreted as MM/DD/20YY

• or the dates would have to be formatted with a 4-digit year, MM/DD/20YY, to be read correctly.

Take a look at your computer's Short Date in Windows / Control Panel / Region / Formats / Additional Settings / Date. See image below from my Windows 8.1 system.

Change your settings to display dates as Short Date with a format of M/d/yyyy

Also change the "When a two-digit year is entered ..." to use a sliding 100-year calendar rule to "2059" or higher. This will force a date MM/DD/18 to be interpreted as MM/DD/2018

Save and apply the changes.

Reboot Windows to make sure the changes are activated.

Now re-import the QIF file into a new empty Quicken data file and see if that gives correct results.

If not, edit the QIF import file with Notepad or any other ASCII text file editor and change all dates from MM/DD/YY to MM/DD/20YY and re-import into a new Quicken data file.

Chris_QPW

UKR said:

In Windows / Control Panel / Region / Formats / Additional Settings / Date. See image below from my Windows 8.1 system.

Change your settings to display dates as Short Date with a format of M/d/yyyy

Also change the "When a two-digit year is entered ..." to use a sliding 100-year calendar rule to "2059" or higher. This will force a date MM/DD/18 to be interpreted as MM/DD/2018

Save and apply the changes.

Reboot Windows to make sure the changes are activated.

Now re-import the QIF file into a new empty Quicken data file and see if that gives correct results.

If not, edit the QIF import file with Notepad or any other ASCII text file editor and change all dates from MM/DD/YY to MM/DD/20YY and re-import into a new Quicken data file.

The short date isn't used for QIF imports.  Changing the Windows settings will have no affect on it.

Both Quicken US and Canadian Windows always use the following format:
MM/DD/YYYY
Or MM/DD/YY for years before 2000 and MM/DD'YY for years from 2000 on.

It is fact a common problem that in other countries they sometimes have the dates like: DD/MM/YYY or DD-MM-YYYY or YYYY-MM-DD, which Quicken US/Canadian will either reject because of a DD is larger that 12 (not a valid month) or you day and the month will be switched in the transaction.

The QIF format isn't a "standard" and as such people/companies/countries have take the liberty of changing things as they saw fit, with the data format being one of the most common.

Web Connect/QFX (a type of OFX file) adhere to the OFX standard and that date format is standard and can be counted on.  It looks like this:
20110128120000[0:GMT]

The fact that can change the date format in Quicken's register and other spots by changing the Windows short date confused thing all the more.

Note my program for CSV/Excel to QIF always puts out MM/DD/YYYY format so that this kind of thing doesn't happen.
http://www.quicknperlwiz.com/

And speaking of "taking liberties" Quicken's special year 2000 (Y2K) format (MM/DD'YY) is definitely one of those cases.  I don't think there is another program that uses that syntax.

Chris_QPW

P.S. the same applies to QIF exports.  Its is a fixed date format.

TownieManufacturing

Ps56k2 - We are using Quicken Deluxe. Our bank is Citizens Bank NE. How we do access old data from our bank to download directly into our Quicken register? Thank you (and everyone) for their suggestions.

GeoffG

90 days is typically all the history most banks provide for download.

Ps56k2

TownieManufacturing said:

Ps56k2 - We are using Quicken Deluxe. Our bank is Citizens Bank NE.

A couple of points....
1 - What Release of Quicken....   Help --> About Quicken

2 - What bank are you downloading ?  answered partial..
3 - Why are you downloading QIF and using Excel - vs just Quicken downloads ?

We like to know EXACT responses - since different things happen within Quicken

When setting up Quicken - you probably did an Add Account - for your bank. But there are literally hundreds of "Citizen Bank" entries - and not sure which one you picked for your specific location.... So, what is the EXACT name on the Quicken Account ?

After you have setup your account in Quicken - it is much more than looking like a spreadsheet - and you should be able to just click and use - One Step Update to download transactions from your bank directly into Quicken....

TownieManufacturing

I think our difficulty is that our Citizens Bank only allows 3 months of direct downloads. We had to go back for 2020 information for that entire year, hence that is why we downloaded it in spreadsheet format and uploaded that to our Quicken.

Chris_QPW

The preferred order is like this:

1. Enable Direct Connect if available.  If not enable Express Web Connect is available.  But for setting up the accounts typical number is 90 days of transactions downloaded.  The financial institution decides on how much history to send.
2. If #1 isn't available or to fill in more history using Web Connect (download and import a QFX file from the financial institution's website) is the next best if available.
3. If not available the QIF is the next best.  Along with this is if the financial institution doesn't support QIF then getting a conversion from a different format like Excel/CSV gives you basically the same (provided the converter works right).
4. If none of that is available, then it is manual entry.

TownieManufacturing

Thank you, one and all, for your guidance and information. Learned that we'd used the incorrect format to download. Now know we need to use Quicken using Webconnect to correctly upload old data to our Quicken. Thank you again for your patience with this new user!