I have created a s/s to record bank transactions, it has been used on an ipad and emailed to me, at which point the dates are as they were entered, but when I then try to copy the sheets to a new workbook the dates mysteriously change altogether, the format changes from DD-MMM-YY to DD-MM-YYYY and they are totally different from the originals. Has anyone any idea what is happening and how to stop it ?
Hi,
I guess this is due to difference in date format between these systems. Can you please post a sample template of your original s/s? You may choose to delete confidential / sensitive information in the file before uploading, if any.
I reckon it's probably the 1904 date system-are the dates about 4 years out?
Josie
if at first you don't succeed try doing it the way your wife told you to
Thanks - not sure if I've done this right . . . should be a sample sheet
Sample ss.xlsx
It's not just the year which changes, but the date and month too !Originally Posted by JosephP
can you give a specific example? note I said about 4 years-it should be 4 years and 1 day so some dates will change day too and some month and day
Oh I see, OK well, 08/05/2013 becomes 09/05/2017 and yeah 31/08/2013 becomes 01/09/2017. Great ! so you know what the problem is - now how do I fix it ?!
in your new workbook enter 1462 into a blank cell and copy it. then select the date cells and choose paste special, then select 'values' and 'subtract' from the options.
It works, absolutely brilliant ! Many, many thanks for that
I've just spotted that in some other workbooks there are "about 4 years " deducted - so presumably instead of ticking "subtract" in the paste special box, I tick "add" ?
yeah-it just depends which way the data was copied
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks