=IF(L5<>"",TODAY()-L5+1,"")

[Michell Major]

This formula has 4 criteria:
Date received (J5)
Date claimed (K5)
Storage Start Date (L5)
Elapsed Days (M5)

The first 2 criteria are self explanatory. Storage start date is initiated
if goods aren't claimed within 24 hrs. I need to stop the elapsed days as
soon as a value is is entered into Date Claimed M5 - at the moment it
continues to count.

 

[Bob Phillips]

So what is wrong with the formula that you give?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Michell Major]

Bob

01/04/2007 03/04/2007 02/04/2007 15

The formula continues to count elapsed days from today's date whereas it
should stop as soon as the date '03/04/2007' is enetered. So the above
should read"1" days' storage not 15.

TIA

Dan

 

[David Biddulph]

=IF(L5<>"",IF(K5<>"",MIN(K5-L5,1),TODAY()-L5+1),"") ?

 

[Michell Major]

David. Thanks. Unfortunately the formula returns a constant value of 1 i.e:
01/04/2007 13/04/2007 02/04/2007 1
Dan

 

[David Biddulph]

Sorry, I was asleep, so my usual confusion between MIN and MAX when I
changed the formula at the last minute. I wanted a minimum limit, so I
should have used MAX.

Try
=IF(L5<>"",IF(K5<>"",MAX(K5-L5,1),TODAY()-L5+1),"")

 

[Roger Govier]

Hi

Try
=IF(J5="","",IF(K5="",TODAY()-L5+1,(K5-L5)))

 

[Michell Major]

David - thanks so much, it works as intended.

Regards

dan