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

rbrookov

New Member
Joined
May 7, 2013
Messages
16
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

SUNSUNMONMONTUETUE
SHIFTEMPSTARTENDSTARTENDSTARTEND
8EMP17:00AM3:00PM10:00AM6:00PM7:00AM3:00PM
8EMP23:00PM11:00PM8:00PM4:00AM3:00PM11:00PM
8EMP311:00PM7:00AM11:00PM7:00AM
EMP4

<tbody>
</tbody>

shiftchart.png
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

apo

Well-known Member
Joined
Nov 3, 2008
Messages
581
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.. :)

Code:
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

New Member
Joined
May 7, 2013
Messages
16
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 :mad:

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!!
 

Forum statistics

Threads
1,171,790
Messages
5,877,508
Members
433,264
Latest member
Donnybrook

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top