Employee Schedule not plotting properly - chart type / data series not correct

[rbrookov]

Hello,

Attached is my spreadsheet (SHIFT_COMPARE) which is supposed to plot the work hours that employees are scheduled to work vs the day they are working, on a weekly basis.

It consists of 3 worksheets: "Const", "Sched", & "Chart".

"Const" = a "back end" sheet containing constants & raw data which I used to create static & dynamic ranges

"Sched" = the data sheet where the manager will select the employee & choose that employee's start time & day(s) they are requested to work

"Chart" = the output of "Sched" in a visual chart showing each employee's work schedule per day and time, on a weekly basis.

For this example, all I'm showing is an abbreviated version of "Sched" (in reality, the work days & shifts are 24/7)

Anyway, I'm having difficulty presenting the chart the way I want it to be presented.

For instance, if Emp1 is scheduled to work on Sun from 7:00 AM to 3:00 PM, I want it to show a bar for Emp1 starting on Sun (X-axis) from 7:00 AM on the Y-axis & goes to 3:00 PM on the Y-Axis. (and likewise for all other Employees & their respective work schedules).

However, it seems to be starting each employee from 12:00 Midnight (rather than their correct start time), as well as plotting the Start Time (i.e, "7:00 AM") as one point vs Midnight and the End Time (ie, 3:00 PM) as another point vs Midnight - obviously it should plot it as one bar going from 7:00 AM to 3:00 PM, and appropriately for each additional day worked, as well as appropriately for each employee.

I'm pretty sure this has to do w/ Excel choosing the data in a series that I'm not wanting - but I'm not sure how to fix it?

While I'm "begging" for help, 1 other (minor, albeit annoying) issue:

1.) the legend creates instances for all employees, regardless of whether they have been scheduled to work that week or not (in my example Employee 4 is not scheduled to work at all, yet he shows up in the Legend).

Any & all help provided will be most appreciated.

Thank you,
Rob
Excel 2003, SP3

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]SUN[/TD]
[TD]SUN[/TD]
[TD]MON[/TD]
[TD]MON[/TD]
[TD]TUE[/TD]
[TD]TUE[/TD]
[/TR]
[TR]
[TD]SHIFT[/TD]
[TD]EMP[/TD]
[TD]START[/TD]
[TD]END[/TD]
[TD]START[/TD]
[TD]END[/TD]
[TD]START[/TD]
[TD]END[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]EMP1[/TD]
[TD]7:00AM[/TD]
[TD]3:00PM[/TD]
[TD]10:00AM[/TD]
[TD]6:00PM[/TD]
[TD]7:00AM[/TD]
[TD]3:00PM[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]EMP2[/TD]
[TD]3:00PM[/TD]
[TD]11:00PM[/TD]
[TD]8:00PM[/TD]
[TD]4:00AM[/TD]
[TD]3:00PM[/TD]
[TD]11:00PM[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]EMP3[/TD]
[TD][/TD]
[TD][/TD]
[TD]11:00PM[/TD]
[TD]7:00AM[/TD]
[TD]11:00PM[/TD]
[TD]7:00AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]EMP4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[apo]

Hi..

I have nearly finished a Workbook that is kinda related..

Its a Rostering Workbook.. and one of the functions uses a Stacked Bar Chart to display a view of the Shifts allocated..

I am thinking you need to allocate a fill color to your chart series that you want to display.. and make the series you want hiden have No Fill.



I am not sure if this will help you directly.. but here is the code i use to set the color of each Bar and also add data labels.. I am only just learning VBA myself.. so i am not 100% sure how i got it to all work.. but, hey.. it works..

Public Sub AddLabels(xDay As Integer)


Dim dayTemp As String


dayTemp = WeekdayName(xDay)




'************************************
'Add First Shift Data Labels
'************************************


         Dim seSales As Series
         Dim pts As Points
         Dim pt As Point
         Dim rngLabels As Range
         Dim iPointIndex As Integer


         Set rngLabels = Sheets(dayTemp).Range(dayTemp & "ChartFirstShiftRange")
         Set seSales = Sheets(dayTemp & "Chart").ChartObjects(1).Chart.SeriesCollection(2)
         seSales.HasDataLabels = True
   
         Set pts = seSales.Points
         
         For Each pt In pts
             iPointIndex = iPointIndex + 1
             pt.DataLabel.Text = rngLabels.Cells(iPointIndex).Text
             pt.DataLabel.Font.Bold = True
             pt.DataLabel.Position = xlLabelPositionCenter


           Next pt
           
           
'************************************
'Set Color of Each Tasks Bar based on Data Label text
'First Shift
'************************************
  
    Dim rName As Range
    Dim iName As Long
    Dim vName As Variant
    Dim iPoint As Long
    Dim vChrValues As Variant
    
    Dim UserColor As Long
    
    Set rName = Worksheets("Tasks").Range("TaskSettings")
    vName = rName.Value
     


    With Sheets(dayTemp & "Chart").ChartObjects(1).Chart.SeriesCollection(2)
        vChrValues = .XValues
        
        For iPoint = LBound(vChrValues) To UBound(vChrValues)
        For iName = 1 To UBound(vName)
            
                If vName(iName, 1) = .Points(iPoint).DataLabel.Text Then
                
                .Points(iPoint).Interior.Color = vName(iName, 2)
  
                End If
                If iName = UBound(vName) Then Exit For
                 
                 If iName >= UBound(vName) Then Exit For
        Next
        Next
        
    End With
    


'************************************
'Add Second Shift Data Labels
'************************************


         Dim seSales2 As Series
         Dim pts2 As Points
         Dim pt2 As Point
         Dim rngLabels2 As Range
         Dim iPointIndex2 As Integer


         Set rngLabels2 = Sheets(dayTemp).Range(dayTemp & "ChartSecondShiftRange")


         Set seSales2 = Sheets(dayTemp & "Chart").ChartObjects(1).Chart.SeriesCollection(4)
         seSales2.HasDataLabels = True


         Set pts2 = seSales2.Points
         For Each pt2 In pts2
             iPointIndex2 = iPointIndex2 + 1
             pt2.DataLabel.Text = rngLabels2.Cells(iPointIndex2).Text
             pt2.DataLabel.Font.Bold = True
             pt2.DataLabel.Position = xlLabelPositionCenter
             
        Next pt2
        
        
'************************************
'Set Color of Each Tasks Bar based on Data Label text
'Second Shift
'************************************
  
  
    Set rName = Worksheets("Tasks").Range("TaskSettings")
    vName = rName.Value
     


    With Sheets(dayTemp & "Chart").ChartObjects(1).Chart.SeriesCollection(4)
        vChrValues = .XValues
        
        For iPoint = LBound(vChrValues) To UBound(vChrValues)
        For iName = 1 To UBound(vName)
            
                If vName(iName, 1) = .Points(iPoint).DataLabel.Text Then
                
                .Points(iPoint).Interior.Color = vName(iName, 2)
  
                End If
                If iName = UBound(vName) Then Exit For
                 
                 If iName >= UBound(vName) Then Exit For
        Next
        Next
        
    End With
    
    UserColor = GetAColorSettings()
   
        End Sub

Hopefully you get some ideas from it...

 

[rbrookov]

Apo,

Hi, thanks for replying. I was hoping to do this with just base Excel (not having to do VBA) but it doesn't like it's possible

I'll take a look at your code, but I'm also a VBA novice as well - and I also need what I think is referred to as "clustered" bars (for each day of the week, there can be 1 or more people working, so I want the number of bars to correspond to the number of people scheduled for each day).

It seems this problem might be more difficult than I originally thought, because no one seems to be able to solve it or willing to tackle it. It's a shame that Excel cannot do such a (what should be) simple plotting feature without having to customize or write code.

I can do something like this in MATLAB very easily.

Anyway, I'll take a look at your code & see if I can use it & modify it for my purposes - again, thank you!!