Unexpected #num return

[Brad]

Excel 2007

On one sheet
=date(2008,13,1) provides the correct answer of 1/1/2009

copy that equation to a different sheet (in the same workbook) provides the
incorrect answer of #NUM!

copy that equation to a third sheet (in the same workbook) provides the
correct answer of 1/1/2009

Can't figure why I'm getting the #NUM!. Any suggestions?

 

[Argy - Arcasoft]

Brad;
No reason for this. Only I can think of is that the sheet is corrupted some
how. Suggestion, delete the second sheet and try on a new one.

Argy

 

[Brad]

The sheet name has been changed on the tab, but the sheets properties name
has not been changed in VBA.

 

[Peo Sjoblom]

You have transition formula evaluation turned on in this particular sheet,
click the office button>excel options
select advanced and scroll to the bottom and under "lotus compatibility
settings for" select this sheet and clear that check mark


In previous excel versions it is under tools>options>transition

--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

DONT do that

--


Regards,


Peo Sjoblom

Brad;
No reason for this. Only I can think of is that the sheet is corrupted
some
how. Suggestion, delete the second sheet and try on a new one.

Argy

 

[Brad]

The only defined name is a print range - which includes the letters Print_Area

 

[David Biddulph]

In Excel 2003 that construct is explicitly allowed. The help for the DATE
function includes:
"Month is a number representing the month of the year. If month is
greater than 12, month adds that number of months to the first month in the
year specified. For example, DATE(2008,14,2) returns the serial number
representing February 2, 2009."

I would be very doubtful whether Excel 2007 has changed this, and your two
other sheets suggest not, so my first thought would be to check whether your
faulty sheet has macros which are confusing the issue?

 

[Brad]

Peo,

Thank you very much!

You have transition formula evaluation turned on in this particular sheet,
click the office button>excel options
select advanced and scroll to the bottom and under "lotus compatibility
settings for" select this sheet and clear that check mark


In previous excel versions it is under tools>options>transition

--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

Nothing of that kind, when using the DATE function to get
the last of a month using for example DATE(2008,13,0)
Lotus couldn't handle that so when formula evaluation is turned on
it will result in a NUM error. I found that the hard way once when I rebuilt
a time sheet
originally created in Lotus 123 and where I used these DATE settings to make
it
change seamlessly into a new year by just change the numbers in one cell I
got
these errors and by quite the coincidence I discovered that it will always
throw a num error
when you manipulate this function using months that don't exist.

--


Regards,


Peo Sjoblom