Sweet Jewiz and all that is holy, Excel, Mac, MS and the date system...

[scooterscot]

Why on earth was this problem not ironed out in the early days?

I've being trying to copy data across from a document generated in MS version of excel using the 1900 date system and of course all the dates change when it's pasted into the Mac version and vice versa, as it uses the 1904 date system.


My problem is similar to:

Excel Date Weirdness | Compound Thinking

There is not quick fix as I've too much data to manipulate

Someone needs a good kick up the $£@£ for this, hanging is too good for them..

[Sysman]

Ouch. Ouch. Ouch.

Can you nail down the Mac version to always default to the 1900 date system?

[scooterscot]

Ouch. Ouch. Ouch.

Can you nail down the Mac version to always default to the 1900 date system?

Indeed I can.

However all the dates already in the sheet leap 4 years and 1 day into the past and suddenly waves on doubt come over me.

Are those dates still correct?

Confidence dwindling...

[Sysman]

• Do you have some reference data to check against? I'd suggest picking some items at random and checking them for accuracy.
• Can you set up some tests so that you can know exactly when and in what circumstances you have "corruption"? This should at least give you some confidence.
• Have you done any sorts on date columns? I you haven't perhaps you have a chance of identifying chunks of data which are off. If you have sorted by date, this may be a nightmare.
• How much work have you done which may be affected? If not a lot, starting again may be an option.
• How important is the data? I've got visions of corporate accounts going seriously adrift here...

[scooterscot]

Thanks for the help.

What I've done is just add the missing days to the serial numbers that represent the dates. There is no other reference I'm aware to verify the data against since I have the original material.

It's knocked my confidence though,