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