Staffing Volume Graph

OK here is my shot at it. It depends on named ranges and offsets.

The first named range is T_0700 =INDIRECT('Contol Panel'!$B$2 & "!$B$3:$BA$3") - This is the row associated with 7 AM for the sheet selected in cell B2.

Once we know this range, we can define other ranges from it. For example 7:30 AM is T_0730 =OFFSET(T_0700,1,0) - this means the same range as T_0700, only a row lower. I had a program generate the other 23 ranges.

Here is more information on named ranges: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges.

Once the ranges are established, then it was simply a matter of summing up how many 1's there were in each row and then plotting that.

As an extra feature, I threw in some data validation in cell B2, so you can select the day of the week from the drop down list to assure you always spell it correctly.

OK, try this. In columns A:C you have the employees and what shifts they are in. This is an excel table, so as you add or delete records from it, the formulas in column F will adjust automatically. Column F is a COUNTIFS to check if the time in column E is between the start and end times.

To clear the data in the table, highlight all the rows in any column(s) in the table, right click and select Delete -> Table Rows.

Then you can copy / paste your data into cell A2. Make sure the start times and end times are actually numeric and not strings that look like times. If they are strings, you'll have to do some "conversion."

Once the data is copied in, everything else happens automatically.

Maybe another two columns to show start and end of the breaks. You may need 4 columns if the person's shift is long enough to need two breaks. Use the same kind of logic to total up this time except subtract it from the gross figure. Having blank times in the break columns (meaning no second or for that matter, first break) should not be an issue. If it is there should be a way to "escape" those rows.