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