Formula or Macro needed

[Debra]

Hello everyone,

Was wondering if I can have some major help here. Bare
with with me on this since I'm kind of new to excel and
don't know if this can be translated into a formula or if
I would have to run a macro, here is what I'm trying to
do. I have dates in the following columns:

Column A: is the date that the item is suppose to arrive
at our facility.
Culumn B: is the revised date if any... of when the item
is suppose to arrive at our facility.
Column C: is the actual date the item arrived at our
facility.

Now what I am trying to do is calculate on time shipments
based on these dates, but some of these dates are missing
and I need a work around for the time being. example:

Original Revised Actual
Due Date Due Date Receipt Date Performance
A B C D

1. 9/29/04 "" 9/27/04 On Time
2. "" 9/26/04 9/27/04 Late
3. 9/30/04 10/01/04 9/27/04 On Time
4. "" "" 9/27/04

I want a formula or macro to run and be placed in column
D, if there are dates missing for column A & B like in
line 4. then I want the formula to return nothing ("")

If the there is a date in Column B, I want the formula to
calculate: =IF(C>B,"Late","On Time") like in line 2, the
same for line 3 regardless if there is a date in column A,
column B comes first than Column A.

If column B has no date but Column A does, then I want the
formula comparing column C against A: =IF(C>A,"Late","On
Time")

I hope this makes sense, any help would be greatly
appreciated.

Thanks

 

[Debra Dalgleish]

Enter the following formula in cell D2:

=IF(IF(B2<>"",B2,A2)<C2,"Late","On Time")

and copy it down to the last row of data.

 

[Debra]

Everything seems to work except when both fields are null
the formula returns on time. Can this be modified so that
the formula can also return a null statement when both
fields are blank?

Thanks

 

[Debra Dalgleish]

You could count the entries in columns A and B:

=IF(COUNT(A2:B2)=0,"",IF(IF(B2<>"",B2,A2)<C2,"Late","On Time"))