Calculate Occupation degree consecutive contracts

tim

New member
Joined
Jul 24, 2013
Messages
18
Reaction score
0
Points
0
Excel Version(s)
2016
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 NumberStart DateEnd DateComment
11/1/20121/5/2012
21/12/201131/8/2012
31/1/201212/2/2013
11/7/201231/1/20132 months gap
11/3/20131 month gap
21/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
 

Attachments

  • Berekening Bezettingsgraad Example2.xlsm
    26.9 KB · Views: 9
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 %
 
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 ...?
 
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.
 
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
 

Attachments

  • Berekening Bezettingsgraad Example2.xlsm
    38.1 KB · Views: 12
Yep all correct but you missed my last paragraph.

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