Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 19th 05, 03:17 PM
dlg1967
 
Posts: n/a
Default Date issue between Windows and Macintosh version

I am at a loss to explain this one and need help to try to resolve. I have a
workbook that was created on a Macintosh and I put the date September 1, 2004
in a cell. I copy the file to a Windows machine and the date shows up
correctly. On the Windows machine, I open a new workbook and copy the date to
the new workbook. The date now changes to October 30, 2000.

What I have found is that Excel for the Mac has a different number in the
cell (I call it the date offset but do not know what it is really called).
The number in the workbook created on the Mac is 36796, where on Windows the
date has the number of 38231. Since the date showed up correctly in the
workbook created on the Mac when I opened it on Windows, I assume this offset
is stored in the workbook. I created a new spreadsheet in the original
workbook on the Windows machine and the date shows up correctly, so that
proved my theory.

Are these offsets supposed to be different? If not, how can I resolve this.
I have Office XP for Windows and Office 2001/Office X for the Mac (OS9 and OS
X). Any ideas?

Thanks much
Dave

  #2   Report Post  
Old January 19th 05, 03:22 PM
JulieD
 
Posts: n/a
Default

Hi Dave

i think you'll find it has something to do with the 1900 or 1904 date system
.... i don't know anything about Macs but if you have a look on your windows
machine under tools / options / calculation - there's a tick box for the
1904 date system ...
by default it is unchecked - but i'm guessing this is what is causing the
problem.

happy hunting

Cheers
JulieD

"dlg1967" wrote in message
...
I am at a loss to explain this one and need help to try to resolve. I have
a
workbook that was created on a Macintosh and I put the date September 1,
2004
in a cell. I copy the file to a Windows machine and the date shows up
correctly. On the Windows machine, I open a new workbook and copy the date
to
the new workbook. The date now changes to October 30, 2000.

What I have found is that Excel for the Mac has a different number in the
cell (I call it the date offset but do not know what it is really called).
The number in the workbook created on the Mac is 36796, where on Windows
the
date has the number of 38231. Since the date showed up correctly in the
workbook created on the Mac when I opened it on Windows, I assume this
offset
is stored in the workbook. I created a new spreadsheet in the original
workbook on the Windows machine and the date shows up correctly, so that
proved my theory.

Are these offsets supposed to be different? If not, how can I resolve
this.
I have Office XP for Windows and Office 2001/Office X for the Mac (OS9 and
OS
X). Any ideas?

Thanks much
Dave



  #3   Report Post  
Old January 19th 05, 03:27 PM
Hari Prasadh
 
Posts: n/a
Default

Hi,

Pasting this from Help

About dates and date systems
Microsoft Excel stores dates as sequential numbers which are called serial
values. By default, January 1, 1900 is serial number 1, and January 1, 2008
is serial number 39448 because it is 39,448 days after January 1, 1900.
Excel stores times as decimal fractions because time is considered a portion
of a day.

Because dates and times are values, they can be added, subtracted, and
included in other calculations. You can view a date as a serial value and a
time as a decimal fraction by changing the format of the cell that contains
the date or time to General format.

Because the rules that govern the way that any calculation program
interprets dates are complex, you should be as specific as possible about
dates whenever you enter them. This will produce the highest level of
accuracy in your date calculations.

Excel supports two date systems: the 1900 and 1904 date systems. The default
date system for Microsoft Excel for Windows is 1900. The default date system
for Microsoft Excel for the Macintosh is 1904. You can change the date
system. On the Tools menu, click Options, click the Calculation tab, and
then select or clear the 1904 date system check box.

The date system is changed automatically when you open a document from
another platform. For example, if you are working in Excel for Windows and
you open a document created in Excel for the Macintosh, the 1904 date system
check box is selected automatically.

Check out "About dates and date systems"

Regular posters would be able to help you better.
--
Thanks a lot,
Hari
India
"dlg1967" wrote in message
...
I am at a loss to explain this one and need help to try to resolve. I have

a
workbook that was created on a Macintosh and I put the date September 1,

2004
in a cell. I copy the file to a Windows machine and the date shows up
correctly. On the Windows machine, I open a new workbook and copy the date

to
the new workbook. The date now changes to October 30, 2000.

What I have found is that Excel for the Mac has a different number in the
cell (I call it the date offset but do not know what it is really called).
The number in the workbook created on the Mac is 36796, where on Windows

the
date has the number of 38231. Since the date showed up correctly in the
workbook created on the Mac when I opened it on Windows, I assume this

offset
is stored in the workbook. I created a new spreadsheet in the original
workbook on the Windows machine and the date shows up correctly, so that
proved my theory.

Are these offsets supposed to be different? If not, how can I resolve

this.
I have Office XP for Windows and Office 2001/Office X for the Mac (OS9 and

OS
X). Any ideas?

Thanks much
Dave



  #4   Report Post  
Old January 19th 05, 03:45 PM
Peo Sjoblom
 
Posts: n/a
Default

Depending on where a workbook has been created just either add or subtract
the dates with 1462, to subtract, put 1462 in a cell, copy it, select the
dates you need to convert and do editpaste special and select subtract
(select add if you need to add the days) So when you open a workbook with
dates created with the default windows system (1900) then subtract 1462,. if
created on a Mac and opened on a windows add 1462. Unless you formatted the
cell with 1462 as a date prior to copying it you need to reformat the
converted result as dates again


Regards,

Peo Sjoblom

"dlg1967" wrote:

I am at a loss to explain this one and need help to try to resolve. I have a
workbook that was created on a Macintosh and I put the date September 1, 2004
in a cell. I copy the file to a Windows machine and the date shows up
correctly. On the Windows machine, I open a new workbook and copy the date to
the new workbook. The date now changes to October 30, 2000.

What I have found is that Excel for the Mac has a different number in the
cell (I call it the date offset but do not know what it is really called).
The number in the workbook created on the Mac is 36796, where on Windows the
date has the number of 38231. Since the date showed up correctly in the
workbook created on the Mac when I opened it on Windows, I assume this offset
is stored in the workbook. I created a new spreadsheet in the original
workbook on the Windows machine and the date shows up correctly, so that
proved my theory.

Are these offsets supposed to be different? If not, how can I resolve this.
I have Office XP for Windows and Office 2001/Office X for the Mac (OS9 and OS
X). Any ideas?

Thanks much
Dave

  #5   Report Post  
Old January 19th 05, 03:51 PM
dlg1967
 
Posts: n/a
Default

This does answer the question. Thanks to you and Julie for responding so
quickly. Now if only I could understand why the same software on two
different platforms would use different start dates. That part floors me, but
I guess I'll have to live with it.

Thanks again!

Dave

"Hari Prasadh" wrote:

Hi,

Pasting this from Help

About dates and date systems
Microsoft Excel stores dates as sequential numbers which are called serial
values. By default, January 1, 1900 is serial number 1, and January 1, 2008
is serial number 39448 because it is 39,448 days after January 1, 1900.
Excel stores times as decimal fractions because time is considered a portion
of a day.

Because dates and times are values, they can be added, subtracted, and
included in other calculations. You can view a date as a serial value and a
time as a decimal fraction by changing the format of the cell that contains
the date or time to General format.

Because the rules that govern the way that any calculation program
interprets dates are complex, you should be as specific as possible about
dates whenever you enter them. This will produce the highest level of
accuracy in your date calculations.

Excel supports two date systems: the 1900 and 1904 date systems. The default
date system for Microsoft Excel for Windows is 1900. The default date system
for Microsoft Excel for the Macintosh is 1904. You can change the date
system. On the Tools menu, click Options, click the Calculation tab, and
then select or clear the 1904 date system check box.

The date system is changed automatically when you open a document from
another platform. For example, if you are working in Excel for Windows and
you open a document created in Excel for the Macintosh, the 1904 date system
check box is selected automatically.

Check out "About dates and date systems"

Regular posters would be able to help you better.
--
Thanks a lot,
Hari
India
"dlg1967" wrote in message
...
I am at a loss to explain this one and need help to try to resolve. I have

a
workbook that was created on a Macintosh and I put the date September 1,

2004
in a cell. I copy the file to a Windows machine and the date shows up
correctly. On the Windows machine, I open a new workbook and copy the date

to
the new workbook. The date now changes to October 30, 2000.

What I have found is that Excel for the Mac has a different number in the
cell (I call it the date offset but do not know what it is really called).
The number in the workbook created on the Mac is 36796, where on Windows

the
date has the number of 38231. Since the date showed up correctly in the
workbook created on the Mac when I opened it on Windows, I assume this

offset
is stored in the workbook. I created a new spreadsheet in the original
workbook on the Windows machine and the date shows up correctly, so that
proved my theory.

Are these offsets supposed to be different? If not, how can I resolve

this.
I have Office XP for Windows and Office 2001/Office X for the Mac (OS9 and

OS
X). Any ideas?

Thanks much
Dave






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to open in windows a macintosh excel file macmai Excel Discussion (Misc queries) 6 January 8th 05 12:44 PM


All times are GMT +1. The time now is 11:26 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017