# Calculate employee utilization based on billed hours

1. ## 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!  Register To Reply

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.  Register To Reply

3. ## 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  Register To Reply

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