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

roborama

New Member
Joined
Oct 12, 2008
Messages
2
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Your formula didn't pass correct, but maybe this give you an idea.


Code:
=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))>
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top