1904 Date Format Issue

[Gregan]

If I have a bunch of dates on a spreadsheet that has the "Use 1904 Date Format" selected, and I want to copy those dates over to a spreadsheet that doesn't have this selected, how do I effectively copy and paste dates between sheets?

If I select or unselect the option on either sheet, then the dates all change to wrong dates.

???

[Richard Buttrey]

Hi,

Use a helper column to add or deduct 4 years to each date, depending on which way you are transferring the dates. Then copy and paste the helper column to the other instance of Excel.

HTH

[Gregan]

That's what it was... I just figured it out.

What a convoluted solution, you think Excel would be able to notice that one spreadsheet was using 1904 and the other 1900 and then figure it out on it's own!

Wow...

Thanks!

[squiggler47]

You'd think excel would get 1900's leap year right so we wouldnt need a 1904 date format!!!!

[DonkeyOte]

You could also use TEXT function to format dates as strings ("dd-mm-yyyy"), copy the resulting values and in the other file coerce strings to dates via Text to Columns.
(we're all assuming of course that none of the dates in the 1904 file precede Jan 1 1904...)

[shg]

Please Login or Register  to view this content.

That was deliberate, to maintain compatibility with Lotus 1-2-3, which launched the error.

[squiggler47]

I know! But still, a check box to use a correct date system might have been as easy!