Confused with Zero Values in IF Commands

[szachary]

Hello All,

I have a field in which a user must manually enter a date. The date field is used to caluclate a number of different cells which have IF Commands in them.

The problem occurs when that field has not been entered it displays the value "0-Jan-00" (likely because I have that column formatted as date). That causes problems with two of my fields which I am not sure how to fix. Specifically it causes the first field (L3) to display On-Time, even though the PO has no date in the field associated with the caluclation, hence this field should state, "Late". The second field (M3) displays the variance in days between the two dates I am measuring, and when there is no second date, it gives a number in the thousands.

What I need to happen is that when an IF command runs into a field it is pulling with no value (aka, 0-Jan-00 when formatted according to date), it will ignores that value. Here are the two columns I am having trouble with:

Column L "Late or Ontime": =IF(K4<=J4,"On-Time","Late")

• For the Late/On-time Field, I need a zero value to default to "Late" without affecting the original calculation should that field be enetered in at a later date.

Column M "Variance (days)": No formula yet

• For the Variance field I need a zero value to be ignored if it is found and the formula to instead calculate the difference between Today's date and the Creation Date (call it I4).

I know I could have done a better job explaining this, but I can't think of any other way to put it. Could anyone help me wrap my head around this????

Thank you!!

 

[TomPC]

You can have multiple IF statements nested together so, assuming your manually-entered date cell is in cell A1, you can say something like:

=if(A1=0,"The date was not entered properly",if(K4<=J4,"On-Time","Late"))

This reads in English like this:

IF A1 equals 0, then say "The date was not entered properly"

OTHERWISE

if K4 is less than or equal to J4, say "On-Time" OTHERWISE say "Late"

Even though the cell is displayed as 00-Jan-00, its value is still interpreted by excel as 0 so the above will work.

Hope that helps

Tom