Problems between the 1900 & 1904 date systems (Mac to Win Users)

[Bugs]

I work for a large company that standardizes on Office for Windows. However, I create a number of spreadsheets on my Mac and by default it chooses the 1904 date system. There would be times when I would forget to change the date system to 1900 and of course when my peers (Win users) copied and pasted from my sheet to theirs, the date was off.

I found a VB add-in (DateV0.9.1.xla) that I found on the internet that would check the date system every time I opened a spreadsheet and ask me if I wanted to convert the date format to 1900. It worked well in Office 2004. Now that I have moved to Office 2008, the add-in no longer works and I get an VB error stating that it won't work in Office 2008.

Does anyone know of a work-around? I have a number of (100's) spreadsheets that were created on the Mac that get interchanged with Win users.

Any way of getting Excel 2008 to open in the 1900 date system as a default?

Thanks for any help you can offer. - Bugs

 

[JE McGimpsey]

Does anyone know of a work-around? I have a number of (100's) spreadsheets
that were created on the Mac that get interchanged with Win users.

There really isn't a workaround, as XL08 does not have events that fire
when workbooks open. However, once you change the date system and save
the file, that system will stick.

You could manually run this script after you open the workbook:

tell application "Microsoft Excel"
set date 1904 of active workbook to false
end tell

Any way of getting Excel 2008 to open in the 1900 date system as a default?

Create a default workbook with the 1904 date system checkbox unchecked -
see Help ("Control how workbooks and sheets are created" topic, "Change
the format and settings for new workbooks" section).

 

[CyberTaz]

Haven't had occasion to test it yet, but in 2008 you should be able to just
create a workbook file with [any of] the settings you want & save it with a
name of Workbook (no extension) in the Excel Startup Folder. Any new
workbooks you create will inherit its settings - unless they're based on
some other file or template.

 

[Bob Greenblatt]

I work for a large company that standardizes on Office for Windows. However, I
create a number of spreadsheets on my Mac and by default it chooses the 1904
date system. There would be times when I would forget to change the date
system to 1900 and of course when my peers (Win users) copied and pasted from
my sheet to theirs, the date was off.

I found a VB add-in (DateV0.9.1.xla) that I found on the internet that would
check the date system every time I opened a spreadsheet and ask me if I wanted
to convert the date format to 1900. It worked well in Office 2004. Now that I
have moved to Office 2008, the add-in no longer works and I get an VB error
stating that it won't work in Office 2008.

Does anyone know of a work-around? I have a number of (100's) spreadsheets
that were created on the Mac that get interchanged with Win users.

Any way of getting Excel 2008 to open in the 1900 date system as a default?

Thanks for any help you can offer. - Bugs

You can do the same thing as this add-in with an XLM add-in. Try setting up
a template as Cyber Taz proposed. Or, using JE's Apple Script. If they don't
work for you, let me know, and I'll send you an XLM macro to do this.

 

[LARS]

CyberTaz's solution outlined above does work. SORT OF. when you first open excel it does launch a workbook based upon the template changes one makes and then saves into the Excel Startup Folder.

However, while in the same excel session you try to creat a new workbook by clicking the "new workbook" icon or selecting "new workbook" from the file menu it reverts back to the hardcoded excel template 1904 date system selected and all.

If you open a new workbook from the project gallery you get the template you changed {from the excel startup folder). Anyone know how to make the new workbook icon reference the template in the excel startup folder?

 

[Thermophile]

MVP's second point addresses your problems:

1, Create a Workbook with the 1900 base date and other standard formats that you want for a workbook and then save in /Applications/Microsoft Office 2008/Office/Startup/Excel as Workbook (template) with no File Extension.

2. Create a single sheet with the 1900 base date and other standard formats that you want for a new sheet and save in /Applications/Microsoft Office 2008/Office/Startup/Excel as Sheet (template) with no file extension.

See Help:Control How Workbooks and Sheets are Created:Change the foremat and settings for new workbooks & Change the format and settings for new sheets.

 

[Dan Bratten]

This worked. Thank you. But most of the settings remain without this step. Wouldn't it be better to have a "Default" button like in Word to set the date (and anything else) as a default setting without having to go through these steps -- which most people will never know exist?

I spent most of the day tracking this down to include a call to MS tech support who had no idea of how to fix the date default. There has got to be a better (intuitive) way.