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

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.

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

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" wrote in message
...
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.

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

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

"Bob Phillips" wrote:

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" wrote in message
...
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.

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

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

"Michell Major" wrote in message
...
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.

"Bob Phillips" wrote:

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" wrote in message
...
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.

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

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" wrote:

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

"Michell Major" wrote in message
...
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.

"Bob Phillips" wrote:

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" wrote in message
...
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.

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

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),"")
--
David Biddulph

"Michell Major" wrote in message
...
David. Thanks. Unfortunately the formula returns a constant value of 1
i.e:
01/04/2007 13/04/2007 02/04/2007 1

"David Biddulph" wrote:

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

"Michell Major" wrote in message
...
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.

"Bob Phillips" wrote:

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" wrote in
message
...
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.

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

Hi

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

--
Regards

Roger Govier


"Michell Major" wrote in
message ...
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" wrote:

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

"Michell Major" wrote in
message
...
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.

"Bob Phillips" wrote:

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" wrote in
message
...
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.

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

David - thanks so much, it works as intended.

Regards

dan

"David Biddulph" wrote:

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),"")
--
David Biddulph

"Michell Major" wrote in message
...
David. Thanks. Unfortunately the formula returns a constant value of 1
i.e:
01/04/2007 13/04/2007 02/04/2007 1

"David Biddulph" wrote:

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

"Michell Major" wrote in message
...
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.

"Bob Phillips" wrote:

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" wrote in
message
...
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.