Hello... I work for at a nonprofit with at risk kids providing therapeutic day treatment. With that being said, I created a spreadsheet to track our client's attendance during the school year and into our summer camp. So, I made a sheet tracking each clinician, their clients, and the hours spent with them each day.
What I need to do is convert their hours per day into billable hours. What that means is this, if we spend 0-1.99 hours with a client we can bill for zero units, 2-2.99 is 1 unit, 3-4.99 is 2 units, and 5 or more is 3 units. And I will need to do that for a range of cells over a period of a few months (ROW 3 "I" - "LT").
Also, to make matters more interesting, the insurance companies (MCOs) that reimburse us give a varied number of units for these time periods (Column D).
So let me explain my sheet some to help you navigate this. ROW 3 starting at "I" till "LT" are the days each month. Column D is the units provided by the MCO. Column F & G is the date range those units are good for. AND... Column E is where I would like to be able to automatically calculate how many units are remaining during that time period. So as my staff fill in the attendance each day that will subtract from the total units in Column D. Could it also be an array of some kind in cell E4 because my sheet changes all the time as kids leave and kids join us?
LAST thing is I converted this from a google sheet so if possible could whatever voodoo excel magic you wizards perform... could it be compatible with google sheets please?!?!
Thank you all for your time in this matter. I am just a novice at all of this and I could really use some help.
All input is welcome.
Bookmarks