Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function returning unexpected value
I am using Excel 2007.
I have the function: =IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]),ISBLANK(Milestones[[#This Row],[Updated Projection Date]])),TODAY()-Milestones[[#This Row],[Baseline Planned Date]],IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]),(ISBLANK(Milestones[[#This Row],[Updated Projection Date]]=FALSE))),TODAY()-Milestones[[#This Row],[Updated Projection Date]],Milestones[[#This Row],[Actual Completion Date]]-Milestones[[#This Row],[Baseline Planned Date]])) I am expecting to get the value of 5 (today - 7/17/2009). Instead, I am getting -39992. Can anyone help me find where I am going wrong? I have looked for so long that I am unable to locate my error. Thank you, -- Paul |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function returning unexpected value
Are you sure that you are subtracting 7/17/09? Try removing the 'TODAY()-'
to see what date you are actually subtracting. If it doesn't change then you must have a cell blank which is then performing a different part of the IF statement. -- Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. Thanks. "Paul" wrote: I am using Excel 2007. I have the function: =IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]),ISBLANK(Milestones[[#This Row],[Updated Projection Date]])),TODAY()-Milestones[[#This Row],[Baseline Planned Date]],IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]),(ISBLANK(Milestones[[#This Row],[Updated Projection Date]]=FALSE))),TODAY()-Milestones[[#This Row],[Updated Projection Date]],Milestones[[#This Row],[Actual Completion Date]]-Milestones[[#This Row],[Baseline Planned Date]])) I am expecting to get the value of 5 (today - 7/17/2009). Instead, I am getting -39992. Can anyone help me find where I am going wrong? I have looked for so long that I am unable to locate my error. Thank you, -- Paul |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function returning unexpected value
Alan,
I took out 'Today()' and nothing changed. I can confirm the following: (1) Baseline planned date = 6/28/2009; (2) Updated Projection Date = 7/17/2009; (3) Actual Completion Date = BLANK I cannot find what is creating the value. Thanks, -- Paul "Alan Moseley" wrote: Are you sure that you are subtracting 7/17/09? Try removing the 'TODAY()-' to see what date you are actually subtracting. If it doesn't change then you must have a cell blank which is then performing a different part of the IF statement. -- Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. Thanks. "Paul" wrote: I am using Excel 2007. I have the function: =IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]),ISBLANK(Milestones[[#This Row],[Updated Projection Date]])),TODAY()-Milestones[[#This Row],[Baseline Planned Date]],IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]),(ISBLANK(Milestones[[#This Row],[Updated Projection Date]]=FALSE))),TODAY()-Milestones[[#This Row],[Updated Projection Date]],Milestones[[#This Row],[Actual Completion Date]]-Milestones[[#This Row],[Baseline Planned Date]])) I am expecting to get the value of 5 (today - 7/17/2009). Instead, I am getting -39992. Can anyone help me find where I am going wrong? I have looked for so long that I am unable to locate my error. Thank you, -- Paul |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function returning unexpected value
"Paul" wrote:
I can confirm the following: (1) Baseline planned date = 6/28/2009; (2) Updated Projection Date = 7/17/2009; (3) Actual Completion Date = BLANK Since you chose to represent your formula in abstract terms instead of copy-and-pasting the actual formula with separate annotation, it is difficult to offer concrete suggestions. Things might not be as they seem, simply because you made mistakes in translating between the actual and posted formulation -- a mistake that anyone can make. But note that -39992 (in your original posting) is the negative serial number corresponding to 6/28/2009. So I would concentrate on where you subtract "baseline planned date", presumably the part of the IF() expression represented by: Milestones[[#This Row],[Actual Completion Date]] - Milestones[[#This Row],[Baseline Planned Date]] Indeed, if the cell containing "actual completion date" is empty, which is my understanding of #3 above, that expression will indeed result in -39992 (i.e. negative "baseline planned date"). Only you can know how to fix your logic to properly cover this state and compute a valid result. But as a wild-ass guess, I wonder if you should use OR() instead of AND() in the initial condition represented by: =IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]), ISBLANK(Milestones[[#This Row],[Updated Projection Date]])) PS: For future reference, Excel provides some tools for debugging formulas. I am not familiar with Excel 2007. But in Excel 2003, you can highlight portions of the formula in the Formula Bar (but be careful not to press Enter, lest you replace the formula as modified!). I prefer to use Tools Formula Auditing Evaluate Formula. ----- original message ----- "Paul" wrote in message ... Alan, I took out 'Today()' and nothing changed. I can confirm the following: (1) Baseline planned date = 6/28/2009; (2) Updated Projection Date = 7/17/2009; (3) Actual Completion Date = BLANK I cannot find what is creating the value. Thanks, -- Paul "Alan Moseley" wrote: Are you sure that you are subtracting 7/17/09? Try removing the 'TODAY()-' to see what date you are actually subtracting. If it doesn't change then you must have a cell blank which is then performing a different part of the IF statement. -- Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. Thanks. "Paul" wrote: I am using Excel 2007. I have the function: =IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]),ISBLANK(Milestones[[#This Row],[Updated Projection Date]])),TODAY()-Milestones[[#This Row],[Baseline Planned Date]],IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]),(ISBLANK(Milestones[[#This Row],[Updated Projection Date]]=FALSE))),TODAY()-Milestones[[#This Row],[Updated Projection Date]],Milestones[[#This Row],[Actual Completion Date]]-Milestones[[#This Row],[Baseline Planned Date]])) I am expecting to get the value of 5 (today - 7/17/2009). Instead, I am getting -39992. Can anyone help me find where I am going wrong? I have looked for so long that I am unable to locate my error. Thank you, -- Paul |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function returning unexpected value
Joe,
That formula is actually the formula used in the spreadsheet (copy and paste). However, I understand I think what you are saying. I am trying to tell it to give me a status on overdue items. In other words, if there is no actual completion date, give me the difference of days between today and the baseline date OR if there is an updated project date then use it instead of the baseline date. I am not good with functions to be honest when it comes to when to use OR or the nested IF's. From my own logic I used the following: IF both actual completion and updated project dates are empty THEN give me the difference of today - baseline date ELSE IF actual completion and updated project date is NOT NULL THEN give me the difference of today - updated project date ELSE give me the difference of actual completion - baseline planned dates. "JoeU2004" wrote: "Paul" wrote: I can confirm the following: (1) Baseline planned date = 6/28/2009; (2) Updated Projection Date = 7/17/2009; (3) Actual Completion Date = BLANK Since you chose to represent your formula in abstract terms instead of copy-and-pasting the actual formula with separate annotation, it is difficult to offer concrete suggestions. Things might not be as they seem, simply because you made mistakes in translating between the actual and posted formulation -- a mistake that anyone can make. But note that -39992 (in your original posting) is the negative serial number corresponding to 6/28/2009. So I would concentrate on where you subtract "baseline planned date", presumably the part of the IF() expression represented by: Milestones[[#This Row],[Actual Completion Date]] - Milestones[[#This Row],[Baseline Planned Date]] Indeed, if the cell containing "actual completion date" is empty, which is my understanding of #3 above, that expression will indeed result in -39992 (i.e. negative "baseline planned date"). Only you can know how to fix your logic to properly cover this state and compute a valid result. But as a wild-ass guess, I wonder if you should use OR() instead of AND() in the initial condition represented by: =IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]), ISBLANK(Milestones[[#This Row],[Updated Projection Date]])) PS: For future reference, Excel provides some tools for debugging formulas. I am not familiar with Excel 2007. But in Excel 2003, you can highlight portions of the formula in the Formula Bar (but be careful not to press Enter, lest you replace the formula as modified!). I prefer to use Tools Formula Auditing Evaluate Formula. ----- original message ----- "Paul" wrote in message ... Alan, I took out 'Today()' and nothing changed. I can confirm the following: (1) Baseline planned date = 6/28/2009; (2) Updated Projection Date = 7/17/2009; (3) Actual Completion Date = BLANK I cannot find what is creating the value. Thanks, -- Paul "Alan Moseley" wrote: Are you sure that you are subtracting 7/17/09? Try removing the 'TODAY()-' to see what date you are actually subtracting. If it doesn't change then you must have a cell blank which is then performing a different part of the IF statement. -- Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. Thanks. "Paul" wrote: I am using Excel 2007. I have the function: =IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]),ISBLANK(Milestones[[#This Row],[Updated Projection Date]])),TODAY()-Milestones[[#This Row],[Baseline Planned Date]],IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]),(ISBLANK(Milestones[[#This Row],[Updated Projection Date]]=FALSE))),TODAY()-Milestones[[#This Row],[Updated Projection Date]],Milestones[[#This Row],[Actual Completion Date]]-Milestones[[#This Row],[Baseline Planned Date]])) I am expecting to get the value of 5 (today - 7/17/2009). Instead, I am getting -39992. Can anyone help me find where I am going wrong? I have looked for so long that I am unable to locate my error. Thank you, -- Paul |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function returning unexpected value
Joe,
I have never used the evaluate formula. That worked perfectly. I was able to target where the formula failed. I needed a NOT in the second check for a updated planned date. Thank you for your help! -- Paul "JoeU2004" wrote: "Paul" wrote: I can confirm the following: (1) Baseline planned date = 6/28/2009; (2) Updated Projection Date = 7/17/2009; (3) Actual Completion Date = BLANK Since you chose to represent your formula in abstract terms instead of copy-and-pasting the actual formula with separate annotation, it is difficult to offer concrete suggestions. Things might not be as they seem, simply because you made mistakes in translating between the actual and posted formulation -- a mistake that anyone can make. But note that -39992 (in your original posting) is the negative serial number corresponding to 6/28/2009. So I would concentrate on where you subtract "baseline planned date", presumably the part of the IF() expression represented by: Milestones[[#This Row],[Actual Completion Date]] - Milestones[[#This Row],[Baseline Planned Date]] Indeed, if the cell containing "actual completion date" is empty, which is my understanding of #3 above, that expression will indeed result in -39992 (i.e. negative "baseline planned date"). Only you can know how to fix your logic to properly cover this state and compute a valid result. But as a wild-ass guess, I wonder if you should use OR() instead of AND() in the initial condition represented by: =IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]), ISBLANK(Milestones[[#This Row],[Updated Projection Date]])) PS: For future reference, Excel provides some tools for debugging formulas. I am not familiar with Excel 2007. But in Excel 2003, you can highlight portions of the formula in the Formula Bar (but be careful not to press Enter, lest you replace the formula as modified!). I prefer to use Tools Formula Auditing Evaluate Formula. ----- original message ----- "Paul" wrote in message ... Alan, I took out 'Today()' and nothing changed. I can confirm the following: (1) Baseline planned date = 6/28/2009; (2) Updated Projection Date = 7/17/2009; (3) Actual Completion Date = BLANK I cannot find what is creating the value. Thanks, -- Paul "Alan Moseley" wrote: Are you sure that you are subtracting 7/17/09? Try removing the 'TODAY()-' to see what date you are actually subtracting. If it doesn't change then you must have a cell blank which is then performing a different part of the IF statement. -- Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. Thanks. "Paul" wrote: I am using Excel 2007. I have the function: =IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]),ISBLANK(Milestones[[#This Row],[Updated Projection Date]])),TODAY()-Milestones[[#This Row],[Baseline Planned Date]],IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]),(ISBLANK(Milestones[[#This Row],[Updated Projection Date]]=FALSE))),TODAY()-Milestones[[#This Row],[Updated Projection Date]],Milestones[[#This Row],[Actual Completion Date]]-Milestones[[#This Row],[Baseline Planned Date]])) I am expecting to get the value of 5 (today - 7/17/2009). Instead, I am getting -39992. Can anyone help me find where I am going wrong? I have looked for so long that I am unable to locate my error. Thank you, -- Paul |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function returning unexpected value
Since you chose to represent your formula in abstract
terms instead of copy-and-pasting the actual formula with separate annotation, Welcome to Excel 2007 and tables! It's called "structured referencing". I would probably never use it unless forced to! -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "Paul" wrote: I can confirm the following: (1) Baseline planned date = 6/28/2009; (2) Updated Projection Date = 7/17/2009; (3) Actual Completion Date = BLANK Since you chose to represent your formula in abstract terms instead of copy-and-pasting the actual formula with separate annotation, it is difficult to offer concrete suggestions. Things might not be as they seem, simply because you made mistakes in translating between the actual and posted formulation -- a mistake that anyone can make. But note that -39992 (in your original posting) is the negative serial number corresponding to 6/28/2009. So I would concentrate on where you subtract "baseline planned date", presumably the part of the IF() expression represented by: Milestones[[#This Row],[Actual Completion Date]] - Milestones[[#This Row],[Baseline Planned Date]] Indeed, if the cell containing "actual completion date" is empty, which is my understanding of #3 above, that expression will indeed result in -39992 (i.e. negative "baseline planned date"). Only you can know how to fix your logic to properly cover this state and compute a valid result. But as a wild-ass guess, I wonder if you should use OR() instead of AND() in the initial condition represented by: =IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]), ISBLANK(Milestones[[#This Row],[Updated Projection Date]])) PS: For future reference, Excel provides some tools for debugging formulas. I am not familiar with Excel 2007. But in Excel 2003, you can highlight portions of the formula in the Formula Bar (but be careful not to press Enter, lest you replace the formula as modified!). I prefer to use Tools Formula Auditing Evaluate Formula. ----- original message ----- "Paul" wrote in message ... Alan, I took out 'Today()' and nothing changed. I can confirm the following: (1) Baseline planned date = 6/28/2009; (2) Updated Projection Date = 7/17/2009; (3) Actual Completion Date = BLANK I cannot find what is creating the value. Thanks, -- Paul "Alan Moseley" wrote: Are you sure that you are subtracting 7/17/09? Try removing the 'TODAY()-' to see what date you are actually subtracting. If it doesn't change then you must have a cell blank which is then performing a different part of the IF statement. -- Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. Thanks. "Paul" wrote: I am using Excel 2007. I have the function: =IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]),ISBLANK(Milestones[[#This Row],[Updated Projection Date]])),TODAY()-Milestones[[#This Row],[Baseline Planned Date]],IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]),(ISBLANK(Milestones[[#This Row],[Updated Projection Date]]=FALSE))),TODAY()-Milestones[[#This Row],[Updated Projection Date]],Milestones[[#This Row],[Actual Completion Date]]-Milestones[[#This Row],[Baseline Planned Date]])) I am expecting to get the value of 5 (today - 7/17/2009). Instead, I am getting -39992. Can anyone help me find where I am going wrong? I have looked for so long that I am unable to locate my error. Thank you, -- Paul |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function returning unexpected value
"T. Valko" wrote in message
... Since you chose to represent your formula in abstract terms instead of copy-and-pasting the actual formula with separate annotation, Welcome to Excel 2007 and tables! It's called "structured referencing". Thanks for the explanation. Initially, I had thought it might new 2007 syntax; but then I thought not when I contemplated "#This Row". Mea culpla! ----- original message ----- "T. Valko" wrote in message ... Since you chose to represent your formula in abstract terms instead of copy-and-pasting the actual formula with separate annotation, Welcome to Excel 2007 and tables! It's called "structured referencing". I would probably never use it unless forced to! -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "Paul" wrote: I can confirm the following: (1) Baseline planned date = 6/28/2009; (2) Updated Projection Date = 7/17/2009; (3) Actual Completion Date = BLANK Since you chose to represent your formula in abstract terms instead of copy-and-pasting the actual formula with separate annotation, it is difficult to offer concrete suggestions. Things might not be as they seem, simply because you made mistakes in translating between the actual and posted formulation -- a mistake that anyone can make. But note that -39992 (in your original posting) is the negative serial number corresponding to 6/28/2009. So I would concentrate on where you subtract "baseline planned date", presumably the part of the IF() expression represented by: Milestones[[#This Row],[Actual Completion Date]] - Milestones[[#This Row],[Baseline Planned Date]] Indeed, if the cell containing "actual completion date" is empty, which is my understanding of #3 above, that expression will indeed result in -39992 (i.e. negative "baseline planned date"). Only you can know how to fix your logic to properly cover this state and compute a valid result. But as a wild-ass guess, I wonder if you should use OR() instead of AND() in the initial condition represented by: =IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]), ISBLANK(Milestones[[#This Row],[Updated Projection Date]])) PS: For future reference, Excel provides some tools for debugging formulas. I am not familiar with Excel 2007. But in Excel 2003, you can highlight portions of the formula in the Formula Bar (but be careful not to press Enter, lest you replace the formula as modified!). I prefer to use Tools Formula Auditing Evaluate Formula. ----- original message ----- "Paul" wrote in message ... Alan, I took out 'Today()' and nothing changed. I can confirm the following: (1) Baseline planned date = 6/28/2009; (2) Updated Projection Date = 7/17/2009; (3) Actual Completion Date = BLANK I cannot find what is creating the value. Thanks, -- Paul "Alan Moseley" wrote: Are you sure that you are subtracting 7/17/09? Try removing the 'TODAY()-' to see what date you are actually subtracting. If it doesn't change then you must have a cell blank which is then performing a different part of the IF statement. -- Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. Thanks. "Paul" wrote: I am using Excel 2007. I have the function: =IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]),ISBLANK(Milestones[[#This Row],[Updated Projection Date]])),TODAY()-Milestones[[#This Row],[Baseline Planned Date]],IF(AND(ISBLANK(Milestones[[#This Row],[Actual Completion Date]]),(ISBLANK(Milestones[[#This Row],[Updated Projection Date]]=FALSE))),TODAY()-Milestones[[#This Row],[Updated Projection Date]],Milestones[[#This Row],[Actual Completion Date]]-Milestones[[#This Row],[Baseline Planned Date]])) I am expecting to get the value of 5 (today - 7/17/2009). Instead, I am getting -39992. Can anyone help me find where I am going wrong? I have looked for so long that I am unable to locate my error. Thank you, -- Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MOD function not returning exact zero | Excel Worksheet Functions | |||
FV Function result is unexpected. | Excel Worksheet Functions | |||
Unexpected Function | Excel Worksheet Functions | |||
The ispmt function is providing unexpected results | Excel Worksheet Functions | |||
Returning colours from a function | Excel Worksheet Functions |