Results 1 to 6 of 6

Thread: Calculate Occupation degree consecutive contracts

  1. #1

    Question Calculate Occupation degree consecutive contracts



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,


    I guess I am looking for some pointers/ideas to calculate in vba an occupation degree in % of our contracts. It should be able to calculate
    this between 2 given dates (start and end date ... say in 2 separate cells)

    In short we have 22 workers ... the excel has following structure below and is organised around the contract number.
    Lines are not ordered ...
    Contract Number Start Date End Date Comment
    1 1/1/2012 1/5/2012
    2 1/12/2011 31/8/2012
    3 1/1/2012 12/2/2013
    1 1/7/2012 31/1/2013 2 months gap
    1 1/3/2013 1 month gap
    2 1/12/2012

    We see a gap of several months of the consecutive contract 1.
    So for contract number 1 there would be a degree of 21/24=87,5% between 1/1/2012 and 31/12/2013.
    This should be done for each contract and in the end a global (for the 22 contracts) should be calculated

    So when someone leaves someone else gets this number .. we see sometimes it's hard to have a 100% occupation of a contract.
    I can't get my head around it ... My VBA knowledge is a little limited. Can someone help me ?

    I guess the logic would be:

    period=end-begin ' calculate the timespan over which the degree is calculated

    For each contractnr
    do
    lookup contract begin and end date before given start date
    nocontract=0 ' count number of days or months no one is in contract
    while endddate contract lies before given end date
    do
    lookup following contract
    nocontract+=begin(this_contract) - end(prev_contract)
    done
    occupation=(period-nocontract)/period*100
    done

    ... but how to do this in vba with excel????? I have already gathered some functions I have
    found on the internet in a module but ... no solution yet
    Attached Files Attached Files

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Does it have to be vba?
    Setup a pivot table, contract, max, min dates and sum calculated field (end - start dates).

    Or vba (sorry typing on tablet)
    For each row
    If contract New redim preserve results(1 to 3, 1 to contract)
    Get min and max dates and difference

    Finally for both solutions difference/(max-min dates) as %

  3. #3
    It doesn't have to be VBA, I never worked with Pivot tables ... but it would be nicer, maybe faster, no ?

    I just tried pivot tables, but how to calculate the difference between end and start dates ...?

  4. #4
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    The problem is actually in the end dates which aren't closed.

    In a new column add Close and copy the formula =IF(end="",INT(NOW()+1),end+1) replacing end with the address (eg c2) and copy the formula down.

    Then create a pivot table, row Contract, values Min(start), max(Close)

    Under (Excel 2010) Options, find fields, Items, sets, click here and go to calculated field add a new field, call it spent and formula = close - start
    Now add Sum(Spent) to your values table

    Finally add a column next to the pivot table for each row = sumspend /(max close - min start) and express as a percentage.

  5. #5

    pivot tables ?

    Ok, i tried it with the above pointers using a pivot table (see attachment) and unless
    I did it wrong this is not the expected result ... now only start and end date of first and
    last contracts in the range are taken if I understand correctly ... I need the gaps between
    the consecutive contracts ... adding the last step i cannot find it however




    .... maybe someone can review what I have done in the setup
    of the pivot table ... maybe i did something wrong ... it does however open up a whole
    new world I used to look only in VBA ... pivot tables DO offer alternatives
    Attached Files Attached Files

  6. #6
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Yep all correct but you missed my last paragraph.

    Daaitabel!e4 = D4/(C4-B4) and copy the formula down :-)

Tags for this Thread

Posting Permissions

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