# Thread: Formula for calculating Utilisation

1. ## Formula for calculating Utilisation

Hi Excel Experts,

I am working on reporting monthly utilisation for the team. The per day cacapcity of 1 person 3 tasks i.e. 15 per person per week.

I am attaching an excel to give you a better understanding. The excel sheet has the following
A report template that has a list box with the names of employees
The total task completed each month
List of holidays take by each employees
List of holidays for the year.

I am looking for the following formula in col D when i select the employee in A4:

Total evaluation / ((Networkdays for the month - Leave for the month for the employee)*3)

The challenge im facing is that the leaves taken by the employee could have the start date of one month and the end date in the subsequent month. In this case i need to get only the leave taken in the particular month.

Looking forward to some great and simple formula.

Regards
Baiju

2. ## Dates in Excel

Hi
In the screen capture C2 contains the formula
=DATE(YEAR(A2),MONTH(A2)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A2),MONTH(A2)+1,0),2)-5))

D2 contains the formula
=IF(C2<B2,NETWORKDAYS(A2,C2),NETWORKDAYS(A2,B2))

I based that on examples from Chip Pearson's site

http://www.cpearson.com/excel/datetimews.htm

Cheers
Geof

3. Hi Geof,

Thanks for this. I was able to get build a formula based on this, however I am faced with the challenge with the leaves of the employee. I also want the formula to be able to let me know the number of leave for the month and also should only take leave for that particular only.

Thanks
Baiju

#### Posting Permissions

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