Prev: Index, Match help
Next: first some general questions about populating...
From: Pat on 2 Apr 2010 13:31
(Excel 2003) Is there a way to calculate the days and then stop the counting
when you have entered a completion date and/or cancelled appears in the
"Status column?

I want the duration column to keep calculating the days until either a date
is enter into the "Completion Date" Column or the "Status" Column has
cancelled or completed in it.

Start Date Completion Date Status Duration
3/24/10 9

3/15/10 3/20/10 Cancelled 5

--
pls
From: Bob Phillips on 2 Apr 2010 13:54

Does the second date automatically mean that the status is also set? If so,
you only need

=IF(B2,B2-A2,TODAY()-A2)

--

HTH

Bob

"Pat" <Pat(a)discussions.microsoft.com> wrote in message
news:4BAA9E91-9FB0-452D-929E-D75C3C64C047(a)microsoft.com...
> (Excel 2003) Is there a way to calculate the days and then stop the
> counting
> when you have entered a completion date and/or cancelled appears in the
> "Status column?
>
> I want the duration column to keep calculating the days until either a
> date
> is enter into the "Completion Date" Column or the "Status" Column has
> cancelled or completed in it.
>
> Start Date Completion Date Status Duration
> 3/24/10 9
>
> 3/15/10 3/20/10 Cancelled 5
>
> --
> pls


From: Mike H on 2 Apr 2010 14:35
Pat,

Apart from the obvious, what is the significance of 'Cancelled' to the
formula when you have a completion date in that row. It seem to me if I
understand correct that this will do what you want

=IF(B2="",TODAY()-A2,B2-A2)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Pat" wrote:

> (Excel 2003) Is there a way to calculate the days and then stop the counting
> when you have entered a completion date and/or cancelled appears in the
> "Status column?
>
> I want the duration column to keep calculating the days until either a date
> is enter into the "Completion Date" Column or the "Status" Column has
> cancelled or completed in it.
>
> Start Date Completion Date Status Duration
> 3/24/10 9
>
> 3/15/10 3/20/10 Cancelled 5
>
> --
> pls
From: Bob Phillips on 2 Apr 2010 14:47
Typo, should be

=IF(B2<>"",B2-A2,TODAY()-A2)


--

HTH

Bob

"Bob Phillips" <bob.phillips(a)somewhere.com> wrote in message
news:uuG5D2o0KHA.840(a)TK2MSFTNGP06.phx.gbl...
>
> Does the second date automatically mean that the status is also set? If
> so, you only need
>
> =IF(B2,B2-A2,TODAY()-A2)
>
> --
>
> HTH
>
> Bob
>
> "Pat" <Pat(a)discussions.microsoft.com> wrote in message
> news:4BAA9E91-9FB0-452D-929E-D75C3C64C047(a)microsoft.com...
>> (Excel 2003) Is there a way to calculate the days and then stop the
>> counting
>> when you have entered a completion date and/or cancelled appears in the
>> "Status column?
>>
>> I want the duration column to keep calculating the days until either a
>> date
>> is enter into the "Completion Date" Column or the "Status" Column has
>> cancelled or completed in it.
>>
>> Start Date Completion Date Status Duration
>> 3/24/10
>> 9
>>
>> 3/15/10 3/20/10 Cancelled 5
>>
>> --
>> pls
>
>


From: Teethless mama on 2 Apr 2010 17:03
> Typo, should be
> =IF(B2<>"",B2-A2,TODAY()-A2)


I don't see anything wrong with your previous answer
> =IF(B2,B2-A2,TODAY()-A2)



"Bob Phillips" wrote:

> Typo, should be
>
> =IF(B2<>"",B2-A2,TODAY()-A2)
>
>
> --
>
> HTH
>
> Bob
>
> "Bob Phillips" <bob.phillips(a)somewhere.com> wrote in message
> news:uuG5D2o0KHA.840(a)TK2MSFTNGP06.phx.gbl...
> >
> > Does the second date automatically mean that the status is also set? If
> > so, you only need
> >
> > =IF(B2,B2-A2,TODAY()-A2)
> >
> > --
> >
> > HTH
> >
> > Bob
> >
> > "Pat" <Pat(a)discussions.microsoft.com> wrote in message
> > news:4BAA9E91-9FB0-452D-929E-D75C3C64C047(a)microsoft.com...
> >> (Excel 2003) Is there a way to calculate the days and then stop the
> >> counting
> >> when you have entered a completion date and/or cancelled appears in the
> >> "Status column?
> >>
> >> I want the duration column to keep calculating the days until either a
> >> date
> >> is enter into the "Completion Date" Column or the "Status" Column has
> >> cancelled or completed in it.
> >>
> >> Start Date Completion Date Status Duration
> >> 3/24/10
> >> 9
> >>
> >> 3/15/10 3/20/10 Cancelled 5
> >>
> >> --
> >> pls
> >
> >
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Index, Match help
Next: first some general questions about populating...