+ Reply to Thread
Results 1 to 3 of 3

Calculate employee utilization based on billed hours

  1. #1
    Registered User
    Join Date
    02-01-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    2

    Calculate employee utilization based on billed hours

    First and foremost, I've done some searching on this forum to find a solution to my problem but haven't located anything yet.

    I would like to develop a spreadsheet template that can be used to calculate my employee's utilization percentages based on their billable hours. I would like it to calculate utilization percentages for period (4-4-5 month schedule), quarter, and year. Ultimately, I would like this spreadsheet to be a template that can be used for each year. We use a time tracking platform that can export an XLS with a summary of each employee's hours for a given date range. This spreadsheet can be copied into my utilization spreadsheet in order to reference the number of hours for each employee. (Although the numbers in the XLS exported from the time tracking program are not formatted as numbers and must have the text converted to numbers first.)

    I've attempted to put together a spreadsheet to do this however, I've run into one problem
    I'm using one sheet to list the total number of hours per period, per quarter, and per year. This sheet also has the total number of holiday hours per period, per quarter, and per year. This seems very manual and the holiday hours for each period would need to be updated from year to year. This sheet is used to reference values for my calculations.
    Period calculation: =(D3+C3)/('2014-Stats'!C27-'2014-Stats'!C8)
    D3 - Billed hours for employee
    C3 - Vacation hours for employee
    '2014-Stats'!C27 - Total hours for period
    '2014-Stats'!C8 - Holiday hours for period

    The calculations for each quarter and the year are basically the same. The problem is that when I run the calculation, it uses total available hours for the entire period (or quarter, or year) so the percentage is inaccurate depending on what day I run the numbers. For example, if someone bills 40 hours the first week of the month and I run the numbers, my formula would give 25% utilization when in reality, they would be at 100% utilization. How can I calculate this properly?

    I'm looking for some assistance on my formulas as well as suggestions as to how I can create a spreadsheet to do this.

    My plan is to also add graphs and charts for a dashboard once I complete the spreadsheet.

    Thanks!

  2. #2
    Registered User
    Join Date
    02-01-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Calculate employee utilization based on billed hours

    Attached is the spreadsheet I've started to develop to help understand what I am trying to achieve.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-27-2013
    Location
    Boulder, CO
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: Calculate employee utilization based on billed hours

    In your Utilization cells, try the following...
    =(B3+C3)/NETWORKDAYS('2014-Stats'!C23,'2014-Stats'!C4,1)

    That will only calculate the number of workdays in that time period.

    Hope that helps.

    -Z

+ 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. [SOLVED] Using Macros to calculate employee hours on a schedule
    By christhweatt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-20-2013, 05:21 AM
  2. Available hours calculation in Utilization!!!
    By nandhamnk in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-05-2013, 12:41 AM
  3. Replies: 0
    Last Post: 03-19-2013, 05:08 PM
  4. Find and calculate employee hours within date range
    By rockytop80 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-14-2011, 11:43 AM
  5. Calculate employee hours for employee evaluation?
    By Triesha in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-09-2006, 10:55 AM

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