# Thread: Calculate Occupation degree consecutive contracts

1. ## 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

2. 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. 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. 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. ## 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

6. Yep all correct but you missed my last paragraph.

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

#### Posting Permissions

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