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!

Improve this question
3
  • 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
    Commented Nov 22, 2017 at 2:30
  • 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
    Commented Nov 23, 2017 at 3:56
  • 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
    Commented Nov 23, 2017 at 11:32

1 Answer 1

Reset to default
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))
Improve this answer
6
  • 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
    Commented Nov 19, 2017 at 1:13
  • 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
    Commented Nov 19, 2017 at 1:24
  • 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
    Commented Nov 19, 2017 at 1:38
  • Answer updated. See the updated formula and give it a try and let me know.
    – patkim
    Commented Nov 19, 2017 at 1:41
  • ...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
    Commented Nov 19, 2017 at 1:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .