Utilization Formula for Billable hours calculated per week and per month

Hi all,

Newby here. I have looked through similar threads and responses however have been unable to find a solution that I can get to work.

I have attached the sheet that I'm using and I just want to have an easy way to show how I am tracking against my targets.

Scenario is:

Hours are billed when a report is completed. I have a KPI of 5 billable hours per day, 25 billable hours per week. (Worked hours each day are the same)
KPI's are tracked and reported against weekly and monthly. The EOM Target is what performance is based on.

I can get my utilisaton to calculate in my tracker for the "whole period, ie: Year" but I can get it to calculate per week, per month etc...

I also need to have the calculation consider available hours for the week. ie: If I am unwell for a day, I would only be available for 20 hours for the week, not 25 hours.

If someone could please assist with this, and if I am ever so lucky, put the formula into my spreadsheet, I would be so very grateful. I've spent far too long trying to get it to calculate properly.

Happy for the tracker to be placed anywhere at the top and to show Weekly and Monthly KPI's please.

Danielle - LMA list Template.xlsx

It doesn't sounds difficult, but it would be nice if you pointed the cells we need to extract information and the expected result.

I'm sorry, of course.

Column K is Billed Hours.

So effectively K needs to be compared against 25 (hours) per week. And "150" per month depending on hours available each week/month. Can be "equal, above, or below" obviously below is a negative.

My biggest issue is getting the formula to count or consider "workdays, per week" and " Workdays, per month" to then have it look at the below:

K (Amount Billed) vs Hours Worked p/week (25), and month.
I don't really want to have to enter Hours worked in the sheet, unless I haven't worked 25, so is it possible for the formula to assume 25 hours per week, unless I tell it to consider another amount?

I may have made less sense now... and this is my problem, I get myself confused and have to start again.

I may be having some cultural problem, I'll try to bring you some helpful formulas to solve your question.

Formula:

Please Login or Register  to view this content.

Calculates the working days less weekend and days you are unwell, UnwellDays is a range.

Formula:

Please Login or Register  to view this content.

Calculates the number of the week.

Formula:

Please Login or Register  to view this content.

Calculates the number of workdays of the month you started the job.

Great thanks so much for that

Glad to help.