How many Mondays, Tuesdays, etc. to date - AGAIN...

I had received help with this in April 2015 (I wasn稚 able to implement this into production until March 2016, though), and thought I did everything correctly, but the Memorial Day holiday demonstrated that I知 missing something. I知 using Excel 2007, and I have a set of worksheets where I知 tracking how many patients were seen on each workday of the week. I create a new set of these sheets every month by changing a few of the cells. The sheet calculates how many workdays there are each month (N20:N24), year to date (O20:O24) and the total for the year (P20:P24). Year to date and total for the year are working correctly, but I see that the workdays per month are not looking at the table in S5:S10, which lists the five legal holidays the facility is closed. I壇 think that the formulae for the year to date could be used with beginning and end dates set for each month, but I知 not sure how to do this. The attached sheet contains no patient data, but does include all of the cells needed for date calculations. Would someone be able to help me with this?

Thank you.

Try

=NETWORKDAYS.INTL($S$14,$S$15,1,$S$4:$S$10)

will give number of workdays (Monday to Friday) in May allowing for holidays.

Not available in Excel 2007 !

Use ...


=NETWORKDAYS($S$14,$S$15,$S$4:$S$10)

Excludes weekends.

Thank you, John. Possibly I wasn't clear in stating that what needs to be changed are the formulae in cells N20:N24. Your formula gives me the number of days worked year to date (which I'm already getting in O20:O24 from a much more complex formula than yours), not just in a specified month.

Thank you, Jason. Your formula appears to do the job. Thanks very much.

Just to clarify: the total number of Mondays worked in May should have been 4 since there are 5 Mondays in May, but one of them was a holiday. Is this correct?

Column N is the number of a particular day of the week worked month to date.
Column O is the number of a particular day of the week worked year to date.
Column P is the number of a particular day of the week that exist for the year.

All minus holidays.

Thank you, John, but when I enter =NETWORKDAYS($S$14,$S$15,$S$4:$S$10) into cells N20:N24, 21 is returned in every cell. As of May 31st, there have been 21 Mondays, Tuesdays, Wednesdays, Thursdays and Fridays in 2016, but the correct result for these cells should be 4. Could I have copied these incorrectly? I copied NETWORKDAYS($S$14,$S$15,$S$4:$S$10) into N20:N24, and then added the "=" to the beginning of each formula. Please know that I do appreciate your efforts.

Thanks again, Jason. So far the data in O20:O24 and P20:P24 have been accurate, but I'll try your formula in these cells.

Dflak, you are correct. We're open every day except for weekends and legal US holidays, so in order to accurately see the patient volume for each weekday, we need to exclude the holidays. Columns N and O will change every day (unless there is a holiday), and column P shows how many workdays for each day of the week (48 Mondays, 52 Tuesdays, etc.) there will be for the entire calendar year. This sheet is updated on the following day, i.e., I'll enter Monday's volume data on Tuesday, Tuesdays on Wednesday, etc.; that is why N17, S4 and S28 add "-1" to the end of the TODAY() function.

To further clarify this, I've attached another sheet that shows what the final report looks like. The data in cells A7:A15, B7:M16 and B20:M24 have either been removed or replaced with the reported values. I've entered Jason's formulae into cells N20:O24.

Thank you, everyone, for your help.

Jason, 21 is the accurate number for the number of Mondays to date in 2016, not 22. What I find odd is when you delete 5/30/2016 from cell S6, a day is added to every day of the week (# of Workdays in May changes from 4 to 5 for every weekday, # of Workdays to Date changes from 21 to 22 for every workday, and # of Workdays in 2016 adds one to every weekday).