Function returning unexpected value

[Paul]

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

 

[Alan Moseley]

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.

 

[Paul]

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

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.

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

 

[JoeU2004]

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 -----

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

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.

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

 

[Paul]

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.

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 -----

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

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.


:

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

 

[Paul]

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

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 -----

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

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.


:

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

 

[T. Valko]

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

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 -----

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

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.


:

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

 

[JoeU2004]

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 -----

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

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 -----

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

:

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.


:

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