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