PDA

View Full Version : Excel 2013 from csv date Query



Neil F
29-07-2014, 10:22 PM
AQ colleague downloads bank files in csv format and emails them to me.

I save them as Excel 2013 and do lots of different sorts e.g by payee or value

I copy these into another Excel 2013 file according to their expense category.

The date transfers inaccurately and copies as 1 day prior to actual day's date and reduces the year by 4

e.g. 19/8/2013 gets copied as 18/8/2009 and 04/06/2014 as 03/06/2010

This occurs whether I use ordinary copy paste or any of the options of Copy paste special.

The year I correct by "find and replace" but the day date is an individual override.

Any ideas?

kahawai chaser
29-07-2014, 11:09 PM
I recall having that issue for graphing a while back. I think you need change the date option to 1904 under tools menu then advanced - calculation. Can't recall exact steps.

kingdragonfly
31-07-2014, 02:10 PM
Probably the "1904" flag

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/why-does-excel-2007-change-dates-when-i-copy-and/b0544a7f-bd82-4b5a-a843-e6ae22f2ef63

http://answers.microsoft.com/en-us/office/forum/office_2010-excel/excel-paste-changing-dates-from-2013-to-2009-minus/497130bf-a4a8-4159-b719-e2c30e4dd697

Neil F
03-08-2014, 10:20 PM
Many thanks kahawai chaser and kingdragonfly.

kahawai I did what you suggested and it works. The pity from my viewpoint is that once having ticked the box in the workbook is no "saving" or "apply" option.

I think I should like to find a "System" Date setting, Now I have tracked down the Microsoft Office "blurb" on this which explained very clearly the difference from 1900 date systems and 1904 date system.
I had not mentioned in my original post that In get the files from an Apple Macintosh user -Numbers. The Microsoft blurb advises that Mac is defaulted to 1904 date system and gives a clear example of the 4years and 1 day variance.

Thanks again for helping me .

Neil