"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