calculate weekdays excluding holidays
I have a spreadsheet that contains start dates and end dates for each record. some end dates are blank if the record is still active. Im looking for a way to calculate the number of weekdays, minus holidays, a client was enrolled in a class.
For instance, a17 - d17 shows that shows that john was enrolled from 8/29/13 - 10/11/13. I want to run a report that shows me the total # of workdays (- holidays) that John was enrolled between 9/1/13 and 9/30/13.
In another example, a18 - d18 shows that Jim was enrolled from 8/26/13 and is still enrolled because they do not have an end date. I want to calculate the number of work days (- holidays) between 9/1/13 and 9/30/13. You should assume that the client is still enrolled because there is no end date.
you can see that in H11 i used the networkdays function in an attempt to solve my problem, but that didnt work exactly as i needed.
see this spreadsheet.
thank you for your help.
For instance, a17 - d17 shows that shows that john was enrolled from 8/29/13 - 10/11/13. I want to run a report that shows me the total # of workdays (- holidays) that John was enrolled between 9/1/13 and 9/30/13.
In another example, a18 - d18 shows that Jim was enrolled from 8/26/13 and is still enrolled because they do not have an end date. I want to calculate the number of work days (- holidays) between 9/1/13 and 9/30/13. You should assume that the client is still enrolled because there is no end date.
you can see that in H11 i used the networkdays function in an attempt to solve my problem, but that didnt work exactly as i needed.
see this spreadsheet.
thank you for your help.
ASKER
Sorry, perhaps those were bad examples
Let's say one person started on 8/26 and ended on 9/15.
Let's say another one started on 9/27 and has no end date.
Could you help calculate the # of billable days.
Let's say one person started on 8/26 and ended on 9/15.
Let's say another one started on 9/27 and has no end date.
Could you help calculate the # of billable days.
Sorry, I am still missing some information:
Are you only trying to calculate billable days for September (9/1/2013-9/30/2013), or all billable days from enroll date to end date?
Are you only trying to calculate billable days for September (9/1/2013-9/30/2013), or all billable days from enroll date to end date?
ASKER
I'd like the billable date range to be a variable. I'd like to be able to set the billable start date and end date and have it calculate the # of weekdays someone was enrolled. So for instance, if someone enrolled on 8/15 and in unenrolled on 9/16, and I wanted to run a report for the month of September, with a start date of 9/1 and an end date of 9/30, I'd want it to calculate that the client was present on 11 weekdays in the month of September.(2,3,4,5,6,9,10,
I hope that makes sense.
I hope that makes sense.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This guy is brilliant!!
I wouldn't say brilliant by any means, but I am glad I could help!
There seems to be some missing information here...
If you are basing the 'billable dates' off of set values, you are always going to return the same answer: 18.