Spreadsheet that will calculate networkdays in each month of a date range with 20 day max

[alanaconda]

I am trying to calculate how many days a teacher has worked in a date range assuming that each month is worth a maximum of 20 days. Weekends do not count. Our school year runs from September 1 to June 30 so it crosses over two years (ex. 9/1/14 to 6/30/15). The entire school year is considered 200 days and works out that each month is worth a maximum of 20 days.

I would like to enter a date range and have an automatic calculation that will break down each month but not exceed 20 days a month (not including weekends). Here is a sample of what I have entered by hand and calculated by hand (attached, I hope)

[Pete_UK]

I remember doing something like that a few years ago for another poster. It's getting a bit late here for me to start looking for it now, but if I get chance tomorrow I'll see if I can find it.

Pete

[alanaconda]

That my friend would be awesome! I look forward to hearing back from you. Happy Holidays!

[Gunther Maplethorpe]

Is this simply a theoretical approach? Or would you like it to reflect reality?

That is, are you working on the assumption that every month does indeed have 20 working days? Or would you like to factor in that some months are longer than others (Feb has fewer than March), and some months have holidays?

[alanaconda]

20 days regardless of holidays or shortened months

[Gunther Maplethorpe]

Hrm. That's going to be a little weird, because the range of 01SEP to 30JUN actually contains 215-218 weekdays (depending on the year), not 200. Trying to force Excel to do bad math only works when you want it to do good math... It would actually be a lot simpler (and more precise) to count the precise number of days or weekdays in a given span.

So, what you really need to do is

1) Calculate the fraction of each month that falls into the given range
2) multiply that portion by 20 days.

Formulae like DATEDIF can calculate the number of complete months within a range; the tricky part is going to be figuring out the portion of partial months at the beginning and end of a range, especially since there will be times when the number of actual weekdays in a given "partial" month will be greater than 20 (for example, the "partial" month of Jan 1 to Jan 29, 2015 still has 21 weekdays in it).

It seems to me that the only way to do that would be to build a table similar to what you have, but it's got to be a different table for every single year that you've got in your range.