Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unexpected #num return
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unexpected #num return
Do you have a defined name date on that sheet? -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=28041 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unexpected #num return
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" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unexpected #num return
The sheet name has been changed on the tab, but the sheets properties name
has not been changed in VBA. "shg" wrote: Do you have a defined name date on that sheet? -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=28041 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unexpected #num return
You have transition formula evaluation turned on in this particular sheet,
click the office buttonexcel 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 toolsoptionstransition -- Regards, Peo Sjoblom "Brad" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unexpected #num return
DONT do that
-- Regards, Peo Sjoblom "Argy - Arcasoft" wrote in message ... 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" wrote: 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unexpected #num return
The only defined name is a print range - which includes the letters Print_Area
"shg" wrote: Do you have a defined name date on that sheet? -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=28041 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unexpected #num return
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? -- David Biddulph "Brad" wrote in message ... 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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unexpected #num return
Peo,
Thank you very much! "Peo Sjoblom" wrote: You have transition formula evaluation turned on in this particular sheet, click the office buttonexcel 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 toolsoptionstransition -- Regards, Peo Sjoblom "Brad" wrote in message ... 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? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unexpected #num return
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 "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... 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? -- David Biddulph "Brad" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculation unexpected | Excel Discussion (Misc queries) | |||
Unexpected Function | Excel Worksheet Functions | |||
Unexpected behavior | Excel Discussion (Misc queries) | |||
unexpected conversion trouble | Excel Discussion (Misc queries) | |||
Unexpected result | Excel Worksheet Functions |