Formatting date in csv for conversion to QIF

2

I am hoping someone might have a solution that will allow me to save a date format in csv as dd/mm/yy. I am converting bank transactions from csv to qif - but when I import into my accounting package, the year always converts to '20'as I think it just reads the first 2 characters of '2017'. If I could save the date in csv as '17' instead of '2017' that would solve the problem - but changing the date format in the cell does not change the underlying format in Excel or CSV which is dd/mm/yyyy.

Any suggestions would be very welcome!

Martin

Posted 2017-11-18T23:59:47.947

Reputation: 21

Have you looked at the .qif file before importing it? Is the date proper there? Is it preceded by a D? Perhaps the problem is with csv>qif converter? – Ron Rosenfeld – 2017-11-22T02:30:58.373

Thanks Ron - I'm not sure how I can check the contents of a qif file without importing it - is there an easy way to do this? I have tried 2 different csv-qif converters and have the same problem with qif files downloaded from 2 banks - so I think the problem is with my version of Quicken. A bit of a dinosaur and of course no support from Intuit...but any other ideas gratefully received! – Martin – 2017-11-23T03:56:01.463

You can check the contents of both the csv and the qif files by opening them in Notepad. If the problem is with Quicken, then this should be directed to a Quicken forum. If you are obtaining the CSV file from your bank, and then using a converter on that file, what does this even have to do with Excel? – Ron Rosenfeld – 2017-11-23T11:32:47.913

Answers

2

Based on what best I understand you question, I am suggesting a solution. Give it a try.

Sample Excel Sheet has data in Row 1 & 2. Column C has date in valid Excel date format say mm/dd/yyyy. Note that in case your date is not in valid Excel format and is stored as just plain Text, a slightly different approach would be required. Now in last cell say in E1 put the following formula.

=CONCATENATE(TEXT(C1,"dd"),"/",TEXT(C1,"mm"),"/",TEXT(C1,"YY"))

Now copy this column elsewhere as Paste Values Only. Say in Column F and clear column E

Clear Entries from original date column say C.

Set the Format of the Column to Text

Copy the temporary date Column F and paste special values into original Column C & clear F

Save the File as CSV.

enter image description here

Sample CVS how it looks now. Note that this is just a sample, original column C is not cleared for the time being. See the last column.

enter image description here

Update

In case CSV when opening back in Excel automatically converts date to default date format, then use this formula instead and check. However this method may affect behavior in any third party application expecting to import CSV.

=CONCATENATE("=",CHAR(34),TEXT(C1,"dd"),"/",TEXT(C1,"mm"),"/",TEXT(C1,"YY"),CHAR(34))

patkim

Posted 2017-11-18T23:59:47.947

Reputation: 3 699

Thank you, thank you, thank you Pat - you are so nearly a genius!! This is a perfect solution - but sadly the csv field formatted as text reverts to 'date' after saving. So your elegant formula was perfect for creating this: 04/10/17 which I saved formatted as text - but when I reopen the csv file it has become this again: 04-10-2017. So close - do you have any idea why this might happen? – Martin – 2017-11-19T01:13:10.000

May be a workaround is that do not double click the CSV file. Open a blank Excel workbook, go to Data -- From Text --> select your CSV file. Now it will ask Text Import wizard. Here select Comma as Delimiter and on next screen Select Text as format for all columns or the intended date column. This will import the CSV into Excel as Text only. It will however internally save the connection in the file that you can remove by going to Connections. See if this helps. Also test how the CSV behaves in your other expected application. – patkim – 2017-11-19T01:24:51.607

thanks I'll give that a try. I had already tried importing from this csv before re-opening and that's when I realised it was still importing the year as '20'...really appreciate your help on this! – Martin – 2017-11-19T01:38:04.480

Answer updated. See the updated formula and give it a try and let me know. – patkim – 2017-11-19T01:41:12.057

...Sadly the workaround didn't work - the import needs the data in columns rather than comma delimited so that didn't work. About to try your new formula - but I think when I paste values with the result I will have the same problem...will let you know! – Martin – 2017-11-19T01:45:39.633

So this time the csv file has retained the formatting ("04/10/17") but when I import into the Quicken it still displays as 04/10/20 - so it looks like the problem might be with Quicken after all. I really appreciate your help on this but it looks like I need to get a Quicken expert on the case now...many, many thanks! – Martin – 2017-11-19T01:55:21.667

Asked: 2017-11-18T23:59:47.947

Viewed: 1 108 times

Active: 2017-11-19T01:40:48.090