Count dates until complete or Cancel
 

(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

Count dates until complete or Cancel
 

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

Count dates until complete or Cancel
 

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

Count dates until complete or Cancel
 

Typo, should be

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


--

HTH

Bob

"Bob Phillips" wrote in message
...

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

Count dates until complete or Cancel
 

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

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




.

Count dates until complete or Cancel
 

Nor do I, but I didn't mean to add that level of um sophistication for
someone who is clearly relatively new to Excel. The amended form is much
clearer in intent.

--

HTH

Bob

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

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




.