permanent conversion of 1904 date format to 1900 date format

[Jos]

I have an old spreadsheet in Windows that has the 1904 date format as basis.
All my other spreadsheets are in the 1900 date format. Are there any
utilities that can permanently convert a spreadsheet from the 1904 to the
1900 date format?

 

[Harlan Grove]

I have an old spreadsheet in Windows that has the 1904 date format as basis.
All my other spreadsheets are in the 1900 date format. Are there any
utilities that can permanently convert a spreadsheet from the 1904 to the
1900 date format?

There's no such thing as permanent conversion.

I'm not aware of any utilities that would do this, but it's fairly
easy to use a macro to do this. Basically, the macro would open a
workbook, check if the workbook is using the 1904 date system, and if
so change it to the 1900 date system and iterate through all used
cells in all worksheets checking for cells containing constant dates
(the cell's .HasFormula property would be FALSE while its .Value
property would be type Date and have value > 1), and when constant
date values were found, add 1462 to them.

 

[Gord Dibben]

I don't know of any utilities but subtracting 1462 from each 1904 date will
give you a 1900 date.

Helper column with =A1-1462 will convert.

Or enter 1462 in an unused cell.

Copy that cell then select the range of date cells and Paste
Special>Subtract>OK>Esc.


Gord Dibben MS Excel MVP

 

[Harlan Grove]

I don't know of any utilities but subtracting 1462 from each 1904 date will
give you a 1900 date.

You want to *ADD* 1462 to 1904-system dates to make them 1900-system
dates.

Think about it for a minute or two.

Then try testing.