Dates change & "Unhide" is erratic when saving Excel Windows file on OS 10.3.2

[Barry D. Brown]

I received a large 9.3 meg Excel Workbook file from a Windows user. I
copied and pasted various records (i. e. complete rows) into a new
Excel Workbook. At this point, the date cells showed properly. I
proceded to hide unneeded columns and saved the file. The dates then
advanced by four years + 1 day and when I tried to unhide "hid"
columns that had previously could be unhid, I found that some refused
to become visible.
I know January 1, 1900 is the start serial date in Windows protocol
and apparently January 2, 1904 is the start serial date in the Mac OS.
A friend advises that in Excel for Windows one can save in either Mac
or Windows date format.
SO, how can I get back the correct dates; get back the hidden columns
and save properly in the future.

 

[JE McGimpsey]

I received a large 9.3 meg Excel Workbook file from a Windows user. I
copied and pasted various records (i. e. complete rows) into a new
Excel Workbook. At this point, the date cells showed properly. I
proceded to hide unneeded columns and saved the file. The dates then
advanced by four years + 1 day and when I tried to unhide "hid"
columns that had previously could be unhid, I found that some refused
to become visible.
I know January 1, 1900 is the start serial date in Windows protocol
and apparently January 2, 1904 is the start serial date in the Mac OS.
A friend advises that in Excel for Windows one can save in either Mac
or Windows date format.
SO, how can I get back the correct dates; get back the hidden columns
and save properly in the future.

First - XL uses the date system of the first workbook opened in a
session. By default on a Mac, that's the 1904 date system. If you
started XL by double clicking on a 1900 date system file, that's the
date system used for the rest of that session (unless you change in in
preferences).

The key when copying is to make sure both the source and destination
workbooks use the same system - it really doesn't matter which one.

To restore your dates, choose Preferences/Calculate and uncheck the 1904
date system checkbox.

As far as the columns, go, unhiding should work if all you did was hide
them in the first place. You might try Format/Columns/Width and giving
them all a non-zero width.

 

[Barry D. Brown]

I had no idea macXL pref>calculate had the 1900 or (by default) 1904
system option. Thank you so much for your help. I will say that
Microsoft ought to show this somewhere in their online help file. I
searched in vain before writing this plea.

Re. getting back hid columns that failed to respond to the "unhide"
command: Are you Superman? Your trick of assigning widths using
Format>Column> Width worked!