The date copies wrong.

msampson

Board Regular
Joined
Mar 9, 2004
Messages
124
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.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

BenMiller

Board RegularThe ONLY cool kid on the block
Joined
Nov 17, 2011
Messages
1,960
Office Version
  1. 365
Platform
  1. Windows
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.
 

tarunscool

New Member
Joined
Jul 10, 2012
Messages
14
Is this in mac or pc..because the dating system in a mac excel is different from a pc excel
 

msampson

Board Regular
Joined
Mar 9, 2004
Messages
124
Office Version
  1. 365
Platform
  1. MacOS
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 =)
 

BenMiller

Board RegularThe ONLY cool kid on the block
Joined
Nov 17, 2011
Messages
1,960
Office Version
  1. 365
Platform
  1. Windows
No problem :) Glad I could help (y) Appreciate the feedback!
 

pinschrk

New Member
Joined
Jul 19, 2012
Messages
1
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,961
Messages
5,599,061
Members
414,281
Latest member
Engjamal2021

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
Top