permanently change the Excel Mac data system

[paolo]

I would like to permanently change the date system of my Excel Mac,
setting the 1900-based date system as default (instead of the
1904-based system).
Is there anybody who knows how to do that???

 

[JE McGimpsey]

I would like to permanently change the date system of my Excel Mac,
setting the 1900-based date system as default (instead of the
1904-based system).
Is there anybody who knows how to do that???

Create a new workbook (format it the way you want, including # of
sheets, styles, etc) and save it *as a template* named "Workbook" (no
quotes, no extension) in your Microsoft Office N:Office:Startup:Excel
folder. All new workbooks will then be based on that template, and XL
will default to 1900 when you open the application directly.

Workbooks saved in the 1904 system will retain that system.

 

[paolo]

Create a new workbook (format it the way you want, including # of
sheets, styles, etc) and save it *as a template* named "Workbook" (no
quotes, no extension) in your Microsoft Office N:Office:Startup:Excel
folder. All new workbooks will then be based on that template, and XL
will default to 1900 when you open the application directly.

Workbooks saved in the 1904 system will retain that system.

That works great! Thank you!
I have another question: on my Mac, I have to open Excel files written
on PCs. Is there a way to open them (on Mac) directly with the 1900
date system, without changing preferences manually each time?
Apparently, the change in the default settings you suggested has no
effect in this case.
Thanks in advance for your help.

 

[Bernard Rey]

paolo wrote :

That works great! Thank you!
I have another question: on my Mac, I have to open Excel files written
on PCs. Is there a way to open them (on Mac) directly with the 1900
date system, without changing preferences manually each time?
Apparently, the change in the default settings you suggested has no
effect in this case.
Thanks in advance for your help.

Here's a link to an Excel Add-in that can set and change the date format,
and converts the dates in the sheet on the fly:
http://www.macbidouille.com/downloads/Datev0.9.1.sitx

It's a French utility, so there may be some issues on a US version of Excel,
but the macros are not protected, so you can adapt them if you like. I
haven't been in touch with the developer, but he'd certainly be glad to have
it adapted to US version of Excel, I imagine.

I intended to do that, but am about sure not to find the time just now...

 

[paolo]

paolo wrote :


Here's a link to an Excel Add-in that can set and change the date format,
and converts the dates in the sheet on the fly:
http://www.macbidouille.com/downloads/Datev0.9.1.sitx

It's a French utility, so there may be some issues on a US version of Excel,
but the macros are not protected, so you can adapt them if you like. I
haven't been in touch with the developer, but he'd certainly be glad to have
it adapted to US version of Excel, I imagine.

I intended to do that, but am about sure not to find the time just now...

The add-in is quite interesting, but unfortunately it keeps the dates

on the sheet unchanged (i.e. 04/07/2008 based 1904 after the conversion
does not become 03/07/2004 but remains 04/07/2008 based 1900...
whereas by unselecting the 1904 option in the XL preferences dates
on the sheet actually change).
Anyway, thanks a lot for your contribution...

 

[Bernard Rey]

paolo wrote :

The add-in is quite interesting, but unfortunately it keeps the dates
on the sheet unchanged (i.e. 04/07/2008 based 1904 after the conversion
does not become 03/07/2004 but remains 04/07/2008 based 1900...
whereas by unselecting the 1904 option in the XL preferences dates
on the sheet actually change).

Yes, that's what the add-in was designed for: keep the dates as they have
been entered, no matter which is the setting on the machine you are using.
If 04/07/2004 has been entered, then you'll read 04/07/2008.

If what you want is to change the dates, in a mis-converted sheet or so, it
should have to be done once to correct it. You can the perform this with a
macro. The lines hereunder could be a way to make it. They're intended to
convert a selected range in a sheet. But you can easily adapt it to convert
a whole sheet, etc.

Sub ConvertingDates()
Dim c As Range
For Each c In Selection ' or ActiveSheet.UsedRange
If IsDate(c) Then
With c
MyFormat = .NumberFormat
.Value = .Value - 1462
.NumberFormat = MyFormat
End With
End If
Next c
End Sub

This way, you can change 04/07/2008 to 03/07/2004