Calculating overdue date fields
 

I have a column with dates that I'd like to use a count formula where it
counts the total number of rows that are past a certain date. How
can I write the formula to calculate this count? Below is an example of what
I mean. A1 is the cell for the date criteria that will determine how many
dates are past due in column B. Column B will have mixture of date and text.
The count should include only "TBD" as overdue when calculating the number
past due.
The answer based on the example below should be 3. Can anyone help me create
this formula?
A1 = 11/25/08

Column B
12/31/08
10/31/08
03/01/13
12/31/08
11/30/08
12/31/08
01/15/09
12/30/08
09/09/09
12/30/08
12/31/08
Not Started
12/30/08
01/30/09
12/31/08
Rqstor on Lv
11/30/08
01/31/09
12/31/08
12/31/08
12/30/08
On Hold
12/30/08
On Hold
12/31/08
12/31/08
10/10/08
01/31/09
12/30/08
11/30/08
1/30/09
4/15/09
TBD
01/30/09
12/31/08
06/30/09
01/31/09
12/15/08
01/31/09

I'm using Excel 2007 and the answer should only be 3 when it counts the dates
before 11/25/08 and "TBD" cells. I want to show only dates that are overdue
and "TBD". How can I get the formula to only count the dates before a date
criteria and "TBD"?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

Calculating overdue date fields
 

=COUNTIF(B:B,"<"&A1)+COUNTIF(B:B,"TBD")
Excel may try to be helpful and display the answer as 3 Jan 1900 - just
format the cell with the formula as General
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"AHizon via OfficeKB.com" <u38169@uwe wrote in message
news:8e5d6c29797a8@uwe...
I have a column with dates that I'd like to use a count formula where it
counts the total number of rows that are past a certain date. How
can I write the formula to calculate this count? Below is an example of
what
I mean. A1 is the cell for the date criteria that will determine how many
dates are past due in column B. Column B will have mixture of date and
text.
The count should include only "TBD" as overdue when calculating the number
past due.
The answer based on the example below should be 3. Can anyone help me
create
this formula?
A1 = 11/25/08

Column B
12/31/08
10/31/08
03/01/13
12/31/08
11/30/08
12/31/08
01/15/09
12/30/08
09/09/09
12/30/08
12/31/08
Not Started
12/30/08
01/30/09
12/31/08
Rqstor on Lv
11/30/08
01/31/09
12/31/08
12/31/08
12/30/08
On Hold
12/30/08
On Hold
12/31/08
12/31/08
10/10/08
01/31/09
12/30/08
11/30/08
1/30/09
4/15/09
TBD
01/30/09
12/31/08
06/30/09
01/31/09
12/15/08
01/31/09

I'm using Excel 2007 and the answer should only be 3 when it counts the
dates
before 11/25/08 and "TBD" cells. I want to show only dates that are
overdue
and "TBD". How can I get the formula to only count the dates before a
date
criteria and "TBD"?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

Calculating overdue date fields
 

Try
=SUMPRODUCT(--(B1:B39<A1))+SUMPRODUCT(--(B1:B39="TBD"))

"AHizon via OfficeKB.com" wrote:

I have a column with dates that I'd like to use a count formula where it
counts the total number of rows that are past a certain date. How
can I write the formula to calculate this count? Below is an example of what
I mean. A1 is the cell for the date criteria that will determine how many
dates are past due in column B. Column B will have mixture of date and text.
The count should include only "TBD" as overdue when calculating the number
past due.
The answer based on the example below should be 3. Can anyone help me create
this formula?
A1 = 11/25/08

Column B
12/31/08
10/31/08
03/01/13
12/31/08
11/30/08
12/31/08
01/15/09
12/30/08
09/09/09
12/30/08
12/31/08
Not Started
12/30/08
01/30/09
12/31/08
Rqstor on Lv
11/30/08
01/31/09
12/31/08
12/31/08
12/30/08
On Hold
12/30/08
On Hold
12/31/08
12/31/08
10/10/08
01/31/09
12/30/08
11/30/08
1/30/09
4/15/09
TBD
01/30/09
12/31/08
06/30/09
01/31/09
12/15/08
01/31/09

I'm using Excel 2007 and the answer should only be 3 when it counts the dates
before 11/25/08 and "TBD" cells. I want to show only dates that are overdue
and "TBD". How can I get the formula to only count the dates before a date
criteria and "TBD"?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

Calculating overdue date fields
 

Perfect, that formula worked. Thanks so much!!

Bernard Liengme wrote:
=COUNTIF(B:B,"<"&A1)+COUNTIF(B:B,"TBD")
Excel may try to be helpful and display the answer as 3 Jan 1900 - just
format the cell with the formula as General
best wishes
I have a column with dates that I'd like to use a count formula where it
counts the total number of rows that are past a certain date. How
[quoted text clipped - 58 lines]
date
criteria and "TBD"?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1