| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| highlight cell with colour if date overdue | New Users to Excel | |||
| how do I calculate 30, 60 90 days overdue from date invoiced | Excel Discussion (Misc queries) | |||
| Can I group overdue Invoices by date from an Excel worksheet? | Excel Worksheet Functions | |||
| need an overdue date to highlight | Excel Discussion (Misc queries) | |||
| Date Overdue function, Macro, or VBS | Excel Discussion (Misc queries) | |||
Linear Mode
