The date copies wrong.

msampson

Board Regular
Joined
Mar 9, 2004
Messages
129
Office Version
  1. 365
Platform
  1. MacOS
I have a huge spreadsheet - over 200,000 rows and over 20 columns. In the original I've made new worksheets with different columns trimmed. I tried to open a new workbook and copy one of the worksheets to it and all the dates copied over as plus one day and 4 years! I did a copy & paste value and verified that the formating is the same. Why is the date wrong and how can I get it to copy properly?
I want a single worksheet in a workbook so I can open it with a different program to look at some stats and I don't want all the original columns. The dataset is too large in it's original format to open several sheets worth in the other program.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Go to options and make sure you're in the 1900 date system, not the 1904 system. Depending on what version of Excel you're using, Google around to see exactly where to find this setting.
 
Upvote 0
Thank you! Found it. Mac using Excel 2008. Under preferences, calculation, uncheck 1904 date box. They all reverted to the right dates.

Love this board =)
 
Upvote 0
No problem :) Glad I could help (y) Appreciate the feedback!
 
Upvote 0
No problem :) Glad I could help (y) Appreciate the feedback!
I have Mac excel 2004 and a big multipage and year spreadsheet. I just tried to copy and past a longish spreadsheet from my benighted brother who uses PC. Of course it pasted with the wrong date (plus one day and four years). I then checked around and found the solution of going to preferences and unchecking 1904. The new spreadsheet is now perfect! But all the other 20 pages are off by one day and four years. On another site someone talked about a technique of typing in 1462 in a blank cell, copying that and pasting special into the date cell, but that did not give me the right options in my version and the best I could get to was copying 52 dates in one cell. Help!

pinschrk
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top