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="","",""))))

On Mar 28, 6:53*am, Potsy <stu...@batchelor-electrical.co.uk> wrote:
> 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.

Errata....

On Mar 28, 11:11*am, joeu2004 <joeu2...@hotmail.com> wrote:
> V334:
> =IF(COUNT(U334,L334),U334-L334,"")

That should be:

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

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")
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
editorial review of Special Sort excel add-in (30 ways to sort)




"Potsy" <(E-Mail Removed)> wrote in message
news:27e5de6e-d0bc-43f6-903f-(E-Mail Removed)...
> 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="","",""))))