Thread: A Formula which Calculates a Single Value from Multiple Criteria

Here's the situation:

I need help creating a working formula that can calculate a variation of information. I have attached a copy of the spreadsheet that I am revising. I work at a company that provides habilitation services which are based on a client's level of need. We use a typical daily schedule that changes with each client. The typical daily schedule contains the # of units (unit = # of hours X 4, 10hrs X 4 = 40 units) a client is allowed to use per day, Sunday - Saturday. I have set up a spreadsheet which calculates the # of weeks, days, units, and cost per unit that a client can use within a given time period. The spreadsheet is suppose to calculate the amount of units within the date range once I input the dates of service and complete the typical daily schedule. For example, if I input a set of dates like 3/14/2010 - 4/30/2010 I get a total of 6 whole weeks and 6 days (days = partial week units). Using the typical daily schedule, I then calculate how many units are needed per day within the 6 whole weeks. And then, I would need to calculate how many partial week units will be needed for the 6 days. There in lies the problem...I setup a formula that tells me which days those 6 partial week units fall between by using 1=Sunday, 2=Monday, etc. But how do I setup a formula that calculates the # of partial week units within those remaining 6 days, if my start day is Sunday and my end day is Friday.

I need to then take those [B]partial week units [/B ]and add them to the total number of units needed for the [B]6 whole weeks[/B and calculate how much it will cost, based on the total units needed within that specific date range, to provide habilitation services to our client.

I would appreciate any help or ideas that you may have!!! Please see the attached spreadsheet for more clarification. I have highlighted in red, the cells that require this formula.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.