Daily Capacity Calculation

Adrian M

Hi,

I'm trying to workout how to create a way to chart our capacity per day using a master sheet which shows the Stop Date and Release Date, to add complications each unit is of a different size and owner which are listed in different columns.

Currently I do it manually; an example of what I need to do -

Stop Date - 01/01/21

Release Date - 13/01/21

Size - 2

Owner - XX

Need an output of for everyday to be charted but this is done for more than 25 units everyday of various sizes and sorted by owners. Every day between 01/01/21 and 13/01/21 would be shown on a chart with 2 added to each day, sorted by owner. The total is usually between 38 to 48 each day once all the units are added up for the days they are stopped.

Hopefully that makes sense!

Thanks

Mark Cronk

Hi @Adrian M ,

You'll need to create a new sheet that will be used to collect the data for your chart. The sheet will have a column of Owners and columns for each day you want plotted. How many days are you charting?

The formula in your day columns would be along the lines of:

=SUMIFS({Size:Size}, {owner:owner}, owner@row, {[start date]:[start date]}, >=today(-X) , {[release date]:[release date]}, <= today(-X)) where names in {} s are external references to those columns on your master sheet. The Today(-X) where X=the number of days in the past you want charted and TODAY(X) is the number of days in the future. So, If your chart depicts 1 month back and 1 month ahead (60 days) you'll have 60 day columns. Your first day column would be TODAY(-30). Your second Column would be TODAY(-29), ... TODAY(),..... continuing to TODAY(30)

Then you'd create a chart of this data table.

Understand? Work?

Mark