BIG Schedule/Gantt problems

**GSAtkinson** · 05-07-2011, 12:12 PM

Friends, Romans, Geeks & Nerds! Lend me your IQs!

I am an analyst. I need formula assistance for tracking labor volume in a 24/7 business. I need to track departmental volume per hour to compare with business volume.

This requires a gantt chart with (instead of conditional formatting a color) a number "1" in the corresponding hour that can be added for total staffing volume for an individual hour.

Here is the rub my friends.
I do not know how to get the correct "1" in the correct cell with the schedule we have. Some staff start at 4PM & end at midnight, some start at 6PM and end at 2AM. Still others start at 10PM and get off at 6AM.

I have included an example of what I have & need.
Any help would be greatly appreciated.

**mrice** · 05-07-2011, 03:16 PM

The attachment has a solution which uses an event driven macro to recompile the Gantt chart every time a time is entered and there is a corresponding start or end time. You can see the code in the Sheet1 tab in the VBA editor (Alt F11)

**arthurbr** · 05-07-2011, 03:23 PM

H GS and welcome to the board.
Is there a particular reason for posting in the forum " Tips and Tutorial" ?

**GSAtkinson** · 05-07-2011, 03:23 PM

Much appreciation Mr. Rice. I was hoping for a formula solution that I could add to the schedule at work, but I will see if I can work this in to it.

The schedule I am using is formatted in a table and has over 400 staffers.

If this works in it, I'll owe you big time.

**GSAtkinson** · 05-07-2011, 03:25 PM

No reason in particular for posting in tips sir. It is just where I landed when I hopped on. Sorry for the mistake. I will try to watch myself future forward.