Hello all,

I am currently doing this task manually in a big spreadsheet, so I'm trying to figure out step by step how to automate the task. Step one requires me to understand how to automatically do the calculation of figuring out how many hours the person worked on a project divided by the # of hours that they could have worked. So for example if

so Joe Smith spent 40/40 hours so his utiliation is 1 = 100%
John Doe on his stream Construction is 26/40 - 65%
Jill is 15% on Construction utilization and 10% on Financial



So basically, on one tab, I enter the hours that someone has worked in the week (their weekly time sheet report) - then on another tab (which is too large and I haven't included yet) we try to do forecast and figure out how much they actually billed. So the tab "Utilization Calculation" I'm hoping there is an easy way to make it automated so I don't have to manually calculate each person every single week.

Appreciate the help in advance.

Attached Files

resource_utilization.xlsx (491.5 KB, 10 views)

A possible solution in edited workbook.

Added a calendar table and a calculated column into 1st table.


Btw, having a table you add a new column per week is a waste and after some time will be cumbersome! Why not design a report sheet, where you select a week (you can use data validation list), and a table for this week is filled (list of active employees in column A, list of active streams at top, and percents in table, plus additional column for person total).

Attached Files

resource_utilization.xlsx (494.6 KB, 9 views)

thanks for the reply, that second option you mentioned sounds interesting, what should I research to work on that solution?

in the meantime I will look at the spreadsheet you uploaded.

I had some idle 30 minutes, so here is an example of report.

The file got too big, I had to zip it for upload.

Attached Files

resource_utilization.zip (473.3 KB, 15 views)

Wow, this is really cool - I really like this idea. I didn't even know this is possible.

Is there a way for me to change the drop down to be something like "Week of Mar 5" instead of "201810"? If that is a manual process, please let me know and I'll take care of it.

In the meantime, I think what I will do is try to combine the various spreadsheets into 2 (combining Streams, Employees, Weeks, and Calendary) to conserve space.

Into table tWeeks you add second column p.e. WeekTxt;
Into this column you enter either week text, or the formula to calculate it. As follows from week start and end dates you used, it looks like you are using ISO week system. As much as I know, there is no official rule for deciding, into which month a week belongs, but I myself have extended the rule the week belonging to year, to where it's Thursday belongs. So the week belongs into month where it's Thursday belongs.
NB! This workbook may serve you for several years, so it is essential in all time references keep the year too - i.e. something like "Week 5 of Mar 2018" for week text - otherwise formulas don't work;
You change the formula for Name ;
In Name Manager, you create a new name RepWeekTxt, which refers to same cell as RepWeekNo;
You edit the Name RepWeekNo: (When you use comma as function parameter delimiter, correct the formula!).

I haven't tested it, but it must be enough. When not, then you at least got the idea.

I myself prefer to keep every table on separate sheet. You can always hide sheets, the user usually don't need - like Calendary and Weeks, which are filled at design time and maybe corrected after some years. And also you can hide sheets you are using only yourself, and not very often (p.e. Employees).

The speed of workbook is determined mostly by size of TimeTable. I think you have to check the speed, when TimeTable exceds 20000 - 30000 rows, but as the workbook don't have too much cells with formulas, then the limit for archiving older entries may be even higher.

Ok so I ended up just entering the date equivalent for the week

i.e 201801 01/01/2018
column a column b

and then simply entered a vlookup in the report column, this tells me easily what week i'm looking at instead of trying to figure out which week is which date.

Really appreciate your help, step 1 is complete, the semi automatic method - next I will work on figuring out the fully automated version (in another thread maybe) and this version of the report will allow me to audit it quickly.

Thanks!! I have marked it as solved