Date Field Subtraction

[Potsy]

Hi

I have problem with Excel (2010) subtracting dates. The date1 is
entered into COLUMN L and a second date entered into COLUMN U when
information sent. The end formula in COLUMN W (below) works fine until
there is no date posted in COLUMN U (i.e. blank) then always sees as -
figure and shows "LATE".

Please help - would like status to show NULL in COLUMN W if nothing in
COLUMN U entered.

Thanks

Potsy

Formula in Column V:

=IF(U334>0,U334-L334,"")

Fomula in Column W:

=IF((V334=0),"ON
TIME",IF((V334<0),"EARLY",IF((V334>0),"LATE",IF((V334="","",""))))

 

[joeu2004]

information sent. The end formula in COLUMN W (below) works
fine until there is no date posted in COLUMN U (i.e. blank)
then always sees as - figure and shows "LATE". [....]
Formula in Column V:
=IF(U334>0,U334-L334,"")

Fomula in Column W:
=IF((V334=0),"ON
TIME",IF((V334<0),"EARLY",IF((V334>0),"LATE",IF((V334="","",""))))

I don't know what you mean by "as - figure". Perhaps you mean that
V334 results in "-" (zero formatted as Accounting?!).

But W334 is always "LATE" because the comparision text>number is
always TRUE, and you are effectively doing V334>0 before V334="".

The following should fix all potential problems:

V334:
=IF(COUNT(U334,L334),U334-L334,"")

W334:
=IF(V334="","",
IF(V334=0,"ON TIME",IF(V334<0,"EARLY","LATE")))

The COUNT function is true only when there is a date in __both__ U334
and L334.

 

[joeu2004]

Errata....

V334:
=IF(COUNT(U334,L334),U334-L334,"")

That should be:

=IF(COUNT(U334,L334)=2,U334-L334,"")

 

[Jim Cone]

Maybe...
=IF(AND(U334>0,L334>0),U334-L334,"NO DATA")
=IF(AND(U334>0,L334>0),IF(V334=0,"ON TIME",IF(V334<0,"EARLY","LATE")),"NO DATA")