linking dates in excell 2000

  • Thread starter Thread starter Siggi
  • Start date Start date
S

Siggi

Hello,

I have a weird problem. I am linking cells that contain
dates between two excel sheets and what happens is that in
the sheet that contains the link is that it automatically
adds four years to all my dates... very strange...

has anybody encountered this problem?

I have tried to change the format of the cells etc. but no
go. This problem only applies to some of the date links I
do not all though...

would be grateful for any comments...

best,

siggi
 
Hi siggi,

One of the spreadsheet in in calendar 1900, the other in calendar 1904.

Menu Tools/Option/Calculation, 1904 date system.
Make sure both are in sync.

Regards,

Daneil M.
 
thanks for you reply...

I tried what you said. what happend is my problem shifted,
that is those sheets that were a prob before were no
longer a prob but others that were ok became a prob. (i am
linking to dates in several different sheets some are 1900
and some 1904)

....if I change the date function on the sheet I am trying
to link to the dates get automatically revieved down. that
is they go down by four years. Is there a way of cecking
the box with out excel revising the dates.... (hope I am
my explanation makes sense...)



thanks again,

Siggi
 
Hi,

You can't link with some 1900 and others 1904.
Dates are represented as numbers. Numbers don't change while linking, but their
representation will (depending on calendar settings).

http://www.cpearson.com/excel/datetime.htm#SerialDates
provides an explanation of your problem.

So, you MUST apply same calendar on all linked workbooks.

Here's some code to fix it produced by a regular here (grabbed from Frederic
Sigonneau french web site).
It does it for one sheet in one workbook.
You can adapt to process all sheets (and even all open workbooks).
Be sure this is the WrkBook initially in 1904 and that you want to convert to
1900 system date.

Sub Date_Fix_From_1904_to_1900()
'JE McGimpsey, mpep
Dim cell As Range

For Each cell In ActiveSheet.UsedRange
With cell
If IsDate(cell.Value) And _
Not cell.HasFormula _
Then .Value = .Value + 1462
End With
Next cell
ActiveWorkbook.Date1904 = False
End Sub


If you want to go from 1900 to 1904, you must substract 1462.

Backup your data!

Hope it helps,

Daniel M.
 
Thanks...!!!

-----Original Message-----
Hi,

You can't link with some 1900 and others 1904.
Dates are represented as numbers. Numbers don't change while linking, but their
representation will (depending on calendar settings).

http://www.cpearson.com/excel/datetime.htm#SerialDates
provides an explanation of your problem.

So, you MUST apply same calendar on all linked workbooks.

Here's some code to fix it produced by a regular here (grabbed from Frederic
Sigonneau french web site).
It does it for one sheet in one workbook.
You can adapt to process all sheets (and even all open workbooks).
Be sure this is the WrkBook initially in 1904 and that you want to convert to
1900 system date.

Sub Date_Fix_From_1904_to_1900()
'JE McGimpsey, mpep
Dim cell As Range

For Each cell In ActiveSheet.UsedRange
With cell
If IsDate(cell.Value) And _
Not cell.HasFormula _
Then .Value = .Value + 1462
End With
Next cell
ActiveWorkbook.Date1904 = False
End Sub


If you want to go from 1900 to 1904, you must substract 1462.

Backup your data!

Hope it helps,

Daniel M.




.
Click to expand...
 
Back
Top