Nested If/And to reveal project status

Using Excel 2003, I'm trying to set up a Status Report column that shows the result after testing two cells in two different columns based on whether or not the cells are blank.

Data in A is the Start Date, B is the End Date.

Conditions for Status Report:
If A(Start Date) is blank, result is "Not Started"
If A(Start Date) is not blank and B(End Date) is blank, result is "In Progress"
If B(End Date) is not blank, result is "Completed"

I have been using <>"" to test for not blank, and ="" to test for blank.

I can get a correct result when only using two of these conditions, but when trying for the third it fails. I think I'm missing something simple in the basic logic with the true/false/if/then inferences but can't "see" it at this time.

I would also like to make it so that if someone enters a value in B without entering a value in A, it is flagged somehow.

Thanks for your help!

If we assume A & B are date values

Please Login or Register  to view this content.

In Excel Dates are just numbers (date serials) and it is only the formatting applied to them that makes them appear as "dates" as opposed to numbers.

Why use SUM ?
SUM will ignore text values - so say A1 contained "apple": =SUM(A1) -> 0

It follows that SUM is a useful way for determining if a cell actually holds a numeric value (and we know this would include dates) - if it doesn't it will return 0 (assuming the cell does not contain an underlying error of course!)

Continuing...

On a 1900 Date System (Windows) the number 0 equates to 0 Jan 1900.

Given we're discussing Projects I think it's fair to say that a genuine date entry in either cell would be > 0, hopefully you agree !

Importantly, in Excel only the number 0 equates to FALSE any other number is equivalent to TRUE, eg:

Please Login or Register  to view this content.

Given all of the above this means we can base our IF on the numerical outputs of our SUM functions.

If a SUM returns 0 we know that realistically no date of interest exists in that cell.

So this:

Please Login or Register  to view this content.

can be "read" as

Please Login or Register  to view this content.

The above is not 100% watertight (negative values etc) but I suspect it will suffice in this instance.

Thanks for stepping me through it! Now I can apply the concept elsewhere rather than just blindly copying and pasting a working formula. Should one of the other users enter something that causes a negative value, I can easily go behind them and correct that. You made my day, thanks!