Count specific day between fixed dates, with user input controlling time frame, month/s

I would post a file but I cannot insert the excel file.

Start Date End Date TOTAL DAYS END DATE MINUS START DATE SATURDAYS END DATE MINUS START DATE SAT in DATE RANGE SUNDAYS END DATE MINUS START DATE SUN in DATE RANGE WEEK DAYS ONLY WEEK DAYS IN DATE RANGE DATE RANGE (W14 and W15)"

=IFERROR(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(G31&":"&F31)))={1,2,3,4,5,6,7})),"0") =IFERROR(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(G31&":"&F31)))=7)),"0") =IFERROR(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(G31&":"&F31)))=1)),"0") =IFERROR(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(G31&":"&F31)))={2,3,4,5,6})),"0") =IF(OR($G31<$W$14,$F31>$W$15),0,ABS(NETWORKDAYS(MIN($W$15,$G31),MAX($W$14,$F31)))) =IF(OR($G31<$W$14,$F31>$W$15),0,ABS(NETWORKDAYS(MIN($W$15,$G31),MAX($W$14,$F31)))) =IF(OR($G31<$W$14,$F31>$W$15),0,ABS(NETWORKDAYS(MIN($W$15,$G31),MAX($W$14,$F31)))) =IF(OR($G31<$W$14,$F31>$W$15),0,ABS(NETWORKDAYS(MIN($W$15,$G31),MAX($W$14,$F31))))

I have trouble for the results for SAT in Date Range and Sun in Date Range

Thank you, dflak

I attached the xlsm file and when I try to open it is says it is a php file. I changed the extension from php to xlsm and it opens in exel.

It looks like you may be going about this the hard way first, take a look at WORKDAYS or NETWORKDAYS. If that doesn't get it maybe one of the formulas in this reference would help http://www.cpearson.com/excel/DateTimeWS.htm.

Here are the instructions on how to attach a workbook.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

To fix the PHP problem (I assume you are using IE). Got to Tools -> Compatibility View Settings and add this site to the list. You might have done that in the past, but a lot of people (self included) got it turned off somehow. Me suspects a Microsoft update to IE as the culprit.

I'll take a look at the file next week. It's getting close to TGIF here

Let me ask about an example. For row 143, the start date is 11/14/2016 and the end date is 12/5/2016. You want to know how many Saturdays and Sundays happen between these dates. I'm confused by the comments and the references to the other dates.

I think walking me through an example or two (a "happy case" and one with an exception) will help me understand what you are looking for better.

So, the dates in the cells is 11/14/16 to 12/5/16. Elsewhere you have a range 12/1/16 to 12/31/16. The range you want to consider is the intersection of these two ranges so you want to know the number of Saturdays and Sundays between 12/1/16 and 12/5/16. Is this correct?

Is the purpose of this to get working days? Or do you need to know the number of Saturdays and Sundays for some other purpose and do you want to account for holidays?

Normally I am a fan of using formulas over VB code, and I probably could do this with a formula, but it would get ugly fast. So if you are willing to accept a VB solution, here it is.

The function, NumDays, takes as its arguments Start Range, End Range, Start Date, End Date, Day of the Week.

So Start Range is W14, End Range is W15, Start Date is Column F, End Date is Column G and Day of Week is either "Sat" or "Sun."

At the very top of the thread select Thread Tools from the menu link and mark the thread as SOLVED.

I'll take a look at this tomorrow.

I think found the offending line of code. Good catch, good write-up and thanks for highlighting the exceptions. All of this made debugging a lot easier.