Gantt Chart - Display start/end time in Excel

[uofte]

First of all - LONG time lurker at Tek-Tips. Thank you all for your help over the years! Searching the forums has really saved my hide over the years. Now, onto my current problem:

Instead of describing the spreadsheet, I have published it at Google Docs. Docs seems to mess with formatting, so d/ling it might make it clearer.

http://spreadsheets.google.com/ccc?key=0AvawEGrxKWKmdDhCbHhwQnlWQ0hPVHV5amVmRkNSUUE&hl=en

Basically, I created a Gantt chart [tab=Schedule] for scheduling employees time. It is accomplished by copy/pasting the red bar into the desired time slots. That chart feeds some basic analytics [tab=Analysis] on another page. It also feeds a summary time sheet [tab=Summary] that shows total hours worked.

I have been asked to expand the spreadsheet to show by employee, by day, the start and end time for the shift. Basically, the need exists to post a simple schedule so the employee can see when they are working, without going through the Gantt chart.

I have posted a manually-keyed version of what I am looking for on the "Summary" tab.

I prided myself on creating a Gantt chart that used a simple method (read: no time formulas), but this new request makes me think that may have come back to haunt me.

Thanks in advance for your help!!!!!!!

 

[SkipVought]

Hi,

When I see this...

It is accomplished by copy/pasting the red bar into the desired time slots.

I KNOW there's a much better method.

My company's internet security prevents me from viewing your example. But I have done hundreds of charts with multiple controls, usually starting with a LIST.

In your case, the list might be the EmployeeList. Insert a Forms Combo Box and link the EmployeeList as the input range.

On a separate sheet (I usually have a sheet for control values that I hide from users) reference the CellLink that will indicate the INDEX of the item selected in the Combo Box. I usually Name the Range something like SelectedEmpIndex.

In a cell adjacent to SelectedEmpIndex, enter this formula...
[tt]
=INDEX(EmployeeList,SelectedEmpIndex,1)
[/tt]
Name this range SelectedEmployee

Assuming that you have already Named the EmployeeList Range using Insert > Name > Create -- Create Names in TOP row

NOTICE: I use Named Ranges extensively!

I also construct my Source Data Table in such a way that I can FILTER via Data > Filter > AutoFilter... in order to show what is selected in the Combo Box. BTW, my source data table is not necessarily very pretty. In most cases, it matters NOT. It's just DATA in a very good useable format! It should have...
[tt]
1. ONE row of unique headings
2. Usually starts in A1
3. No EMPTY ROWS
4. NO EMPTY COLUMNS
5, NO OTHER data in any row or column 'touching' the table (3-5 mean that ALL data is contiguous)
[/tt]
Turn on your macro recorder and record setting the filter for an employee.

alt+F11 will TOGGLE between the VBA Editor and your Excel sheet.

Look at the code that you recorded. Where your code is something like this...

Criteria1:="John Doe"

change to this...

Criteria1:=[SelectedEmployee]

Change your [red]MacroName[/red], Sub [red]MacroName[/red](), to whatever name you like.

alt+F11 back to your sheet, Right-click the Combo Box, and select Assign macro and assign your new macro from the selections available.

Now each time you select a name in the combo box, the chart source data will be filtered and only the VISIBLE data will be displayed on the chart.

Select the Chart Title. Select in the Formula Bar and type...
[tt]
=YourControlSheet!SelectedEmployeeCellRef
[/tt]
puts the Employee Name on the Chart.

You can use other controls to control the time frame displayed, for instance.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[uofte]

Skip,

Thanks for the thorough reply. I have read through it a couple of times (and will do so again just to make sure I understand it all), but I don't think it addresses what I am speaking of.

I know you stated that you cannot d/l the spreadsheet. That may be a hindrance here. Is there any way I can get it to you?

The purpose of copy/pasting the cell is to give the manager who is filling out the schedule a very easy way to do so. Think of it like this, on paper it is similar to them using a highlighter to fill in some boxes on a pre-printed grid. It is their only step.

Here is a text representation, where xxxx is the copy/pasted cell

TIME
NAME 01am 0130 0200 0230 0300 0330
JDoe blank xxxx xxxx blank blank blank
MSmith blank blank xxxx xxxx xxxx blank


In this case, JDoe would be scheduled to work from 0130 to 0230. MSmith from 0200 to 0330. The manager's interaction has been just pasting (trust me - they need it simple ).

In the background, the xxxx cell contains the number 1 (one), hidden from view. The #1's are then summed up to get the total hours worked on the schedule page.

I now need a numerical representation showing the time. In other words it needs to look like this:

Name Total Hours Monday Tuesday
Start End Start End
JDoe 1.0 0100 0200
MSmith 1.5 0200 0300



I don't know how to get the beginning and end time to appear. Hopefully I explained it clearly.

Thanks,
Dustin

 

[SkipVought]

[tt]
TIME
NAME 01am 0130 0200 0230 0300 0330
JDoe blank xxxx xxxx blank blank blank
MSmith blank blank xxxx xxxx xxxx blank
[/tt]
1. You must include in the DATA of the headings of this table, the DATE & TIME. You can FORMAT to make it APPEAR like you have it, but the Date & Time MUST be there.

2. I don't have the time to work out a specific solution, but here's the outline to get the Start/End Date/Times:

you know how many 1/2 hr time slots there are in each day. Let's say its 10. Monday will be the first 10, Tue the second 10, etc.

Put a 0 above Mon, 1 above Tue etc. This will be a multiplier of number of slots.

Use the OFFSET fuction to define the Date/Time value for that day =OFFSET($A$1,0,1,1,DayMult*10) and a similar one to desing the range for an employees assigned slot(s) for that day =OFFSET($A$1,0,1,MATCH(EmpVal,Name,0),DayMult*10) where I use Name as a Named Range from your Name Column.

Use this array in conjuction with INDEX & MATCH functions to locate the start date/time and end data/time.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

It started to come to me as I posted the previous reply...
[tt]
B14 0 0 1 1
Name TotalHours Mon Mon Tue Tue
Start End Start End
JDoe 1.5 8/20/2009 1:30 8/20/2009 2:00 8/21/2009 1:00 8/21/2009 1:00
Msmith 3.5 8/20/2009 2:00 8/20/2009 3:00 8/21/2009 1:30 8/21/2009 3:00
[/tt]
[tt]
D17: =INDEX(OFFSET($A$1,0,D$14*SlotsPerDay+1,1,SlotsPerDay),1,MATCH(1,OFFSET
($A$1,MATCH($B17,NAME,0),D$14*SlotsPerDay+1,1,SlotsPerDay),0))

E17: =INDEX(OFFSET($A$1,0,E$14*SlotsPerDay+1,1,SlotsPerDay),1,MATCH(1,OFFSET
($A$1,MATCH($B17,NAME,0),E$14*SlotsPerDay+1,1,SlotsPerDay),0))+SUM(OFFSET
($A$1,MATCH($B17,NAME,0),E$14*SlotsPerDay+1,1,SlotsPerDay),-1)/48
[/tt]



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]