Solved: Dynamic Capacity Analaysis

[dek]

Hi,

Overview
I am looking to populate capacity values in the date range (column H: to end date range) for each asset type.

Sample Workbook
The attached sample workbook (excel 2003) outlines the target result (based on individual cell input by me) for the capacity values.

Goal:
I have a list of 100 assets to monitor (sample workbook shows 6).

I am trying to manage the capacity up to 6 months out (sample workbook shows 1 month only).

I wish to remove the manual process of inputting the values through a VBA process if possible.

Workbook Definitions:
The capacity value is the "shifts" value
The capacity value is populated from the "roster" type using the date range (start and end date).
The roster definitions are:
14/7C - working continuously (every day)
14/7NC - working 14 days, 7 days off (non continuous)
5/2NC - working 5 days, 2 days off (non continuous).

Help appreciated.

[Kenneth Hobs]

How are you going to handle holidays?

[someboddy]

What you need is Clock_arithmetic (http://en.wikipedia.org/wiki/Clock_arithmetic). You do it with a worksheet function called MOD that calculates the reminder. You can use it to get the place of a number in a circularity, and than check if that place is on the rooster. Here is an example for asset1 - you put this formula in H3 and stretch it to the right:
=IF(MOD(H$2+7,21)<7,"",2)
21 is the circularity's length(14+7), and you check if it's less than 7(0-6: the week off) your return a zero length string(or 0 if you like), otherwise(7-20: the week on) you return 2(the number of shifts). the reason I added 7 to the date is to align the cycle with the one you provided(for asset2 you add 5 instead of 7, for example)

[dek]

Hi,

Holidays are not applicable.

The mod function works a treat. Thank you.