1. Home
  2. Quicken Personal Finance
  3. exporting/importing QIF files

exporting/importing QIF files

I've posted earlier on this but it seems easier now to start a new thread to start from where I've reached.

I am in the process of moving from the UK version of Quicken 2000 deluxe to

2008 Home & Business. And at the same time moving to Vista 64 bit. And I am almost there!!!

With help from this group, and especially from John Pollard, I've been able to get printing working, to set 2008 up to use sterling as the default currency, and learned how to export QIF files from the old version, then import those into a temporary cash account, and then copy all those transactions into the real account. Wonderful!

Except for the date field!! This is the final issue.

The imported data in 2008 is in the FORMAT I want ( i.e. DD/MM/YYYY - the standard for us Brits) but YYYY shows as 19** instead of 20** (e.g 1908 instead of 2008).

I've tried using the Excel macro xl2qif. When I import a QIF file into it, the dates show as they should (i.e. 2007 for instance, not 1907), but when I try to save as a new QIF file I keep getting error messages "Invalid dates in date column". I've made sure that the data range selected does not include column headings when I've been doing this. I am using Excel 2007.

In case it's relevant I've noticed that in the 2000 version of Quicken, dates in the transaction windows show as DD/MM/YY, whereas in the 2008 version they show as DD/MM/YYYY. I've searched around Quicken to find a date display/format setting but couldn't find one - if I could just have the last two digits of YYYY displayed there would be no further problems.

All suggestions welcome.

Reply to
JIP
Loading thread data ...

That message box also will give you the cell where the error is found and cell value.

Are you sure you have correctly defined the format of the data for outputting? That you have the correct column designated for the date (and all other fields)?

Reply to
John Pollard

I should have mentioned in my previous reply that:

1.) Quicken's date format is controlled by the System Date format (Control Panel in XP). 2.) Quicken stores its dates with a 4 digit year (actually two digit century + two digit year). If you could only see the year and not the century, your Quicken dates would still be wrong ... you just wouldn't be able to see the problem.
Reply to
John Pollard

Hmmm - well the cell reference that is cited is b4, with a value of 100, but that isn't the date column.

When I import the QIF file into excel, the columns are in the order Date, Amount, Cleared Status, Check Number, Payee, Category, Sub-category, Memo.

Once the file has been imported, I have then selected the whole data range (not including column titles) and selected Save to QIF, then ticked the fields to include (such as date, amount etc) then clicked on "Convert". That is when I get the message "Invalid values in Date column B4: 100"

But column B is not "date"

Am I supposed to move the columns into the order in which they appear when I am selecting which fields to include?

Or is there something else I'm missing?

Thanks again

Reply to
JIP

Sort of.

It is the order in which you "tick" the fields to output that determines their "sequence" in the spreadsheet. The first field you "tick" must be the leftmost field (column), the second field you "tick" must be the next leftmost field (column), etc. If the first field you put a check mark in was "Check Number", "xltoqif" would assume that the leftmost field/column was "Check Number" ... even if that column was really the date.

If you have ticked the fields in the wrong order, just click the "Clear" button, and initiate the selection process again, with particular attention to the order in which you make the selection. When you have finished the field selection process, look below at the "Column order in Input Data" box to verify that you have selected correctly.

Reply to
John Pollard

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.