Counting the number of days between dates, including a blank cell?

[roborama]

On one of my weekly reports, I need to count all service requests that are "Past due", "Due this week", "Due next week", "Due in 3 weeks", etc. The counts are populated into a summary at the top of the worksheet.

Column G contains the due date of the request.

Columns T and U are part of the table array of dates. i.e 10/13/08 to 10/19/08 is due this week, 10/20/08 to 10/26/08 is due next week, etc.

My problem is if a service request does not have a date, it leaves the cell in column G blank. That makes the service request excluded from the count of "Past due" items, making my report inaccurate.

It is not possible to add a due date in the blank cell, due to the nature of the the database from which I gather data.

Is it possible to include a blank cell in this formula?

I use this formula for "Past Due" service requests in the summary at the top of the worksheet:
=SUMPRODUCT((G$9:G$203>T$1)*(G$9:G$203<U$1+1))
T1 is 10/13/08 U1 is 10/19/08
so, any date less than 10/13/08 is considered past due.

I like this formula, because it will work for all intervals which need to be counted. All except for the blank cell issue. It's driving me nuts. Any help is appreciated.

 

[sailepaty]

Your formula didn't pass correct, but maybe this give you an idea.

=SUMPRODUCT(((G$9:G$203>T$1)*(G$9:G$203 < U$1 )) + ISBLANK(G$9:G$203))<?XML:NAMESPACE PREFIX = U$1))+ISBLANK(G$9 /><U$1))+ISBLANK(G$9:G$203))<U$1+1)) p <>

Regards
</U$1))+ISBLANK(G$9:G$203))<U$1+1))>

 

[roborama]

Thank you for your reply. Works like a charm. You are my hero!!
Kathleen