+ Reply to Thread
Results 1 to 6 of 6

Need to convert hours to billable units and then subtract from the total units provided

  1. #1
    Registered User
    Join Date
    04-06-2020
    Location
    USA
    MS-Off Ver
    MS Office 365
    Posts
    5

    Question Need to convert hours to billable units and then subtract from the total units provided

    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.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,336

    Re: Need to convert hours to billable units and then subtract from the total units provide

    Welcome to the forum.

    Which platform are we talking about? Excel for Mac?

    EDIT: Sorry - just seen that you said Google Sheets.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    04-06-2020
    Location
    USA
    MS-Off Ver
    MS Office 365
    Posts
    5

    Re: Need to convert hours to billable units and then subtract from the total units provide

    Any wisdom would be helpful... I don't even understand the language in order to figure this out myself. Usually, when there is something cool I want to add or create on my sheet I can just look it up. But I'm not even sure I know what needs to happen here. Please help!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Need to convert hours to billable units and then subtract from the total units provide

    As both SUM and SUMPRODUCT are listed as google functions, I believe that the following will work for cells E4 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    04-06-2020
    Location
    USA
    MS-Off Ver
    MS Office 365
    Posts
    5

    Re: Need to convert hours to billable units and then subtract from the total units provide

    This is it... thank you kind sir!!!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Need to convert hours to billable units and then subtract from the total units provide

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Units installed per hours?
    By joffett in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-04-2019, 07:54 PM
  2. Convert units and units of measure
    By AntiPivotTable in forum Excel General
    Replies: 1
    Last Post: 10-21-2016, 07:22 PM
  3. [SOLVED] Formula to determine number of units at each location and status of units
    By Klimer in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-18-2016, 10:01 AM
  4. Trying to do conditional counting, units within units
    By Butcher1 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-17-2016, 01:01 PM
  5. [SOLVED] Calculate the Bonus units according to the quantity of the units bought (Help)
    By mo_naf in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-15-2012, 05:51 PM
  6. Total Units for each Customer
    By Boozik in forum Excel General
    Replies: 2
    Last Post: 02-03-2009, 03:07 PM
  7. [SOLVED] convert units
    By luiss in forum Excel General
    Replies: 4
    Last Post: 02-20-2006, 05:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1