Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    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
    Attached Images Attached Images
    Last edited by geofrichardson; 2011-04-06 at 00:53.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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
  •