Graph Showing Staff Away over holiday period + how many away from each team

VirtualInsanity

New Member
Joined
May 23, 2012
Messages
23
Office Version
  1. 365
Platform
  1. Windows
Hi there,

I've been going round in circles trying to figure this one out. I have to show on a graph staff on leave during a certain period, including how many per team. I'm looking for a graph with staff names on the vertical axis and each date on the horizontal axis then a plot mark showing who is away on which date, with a bar graph overlayed showing how many people from each team are away on each date. I'm not sure how to present the data on a table so the graph will work but I thought something like this might work (where the team name indicates that person is away on that date). Any help appreciated!


[TABLE="width: 563"]
<colgroup><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]John[/TD]
[TD]Mark[/TD]
[TD]Susan[/TD]
[TD]Ronald[/TD]
[TD]Andrea[/TD]
[/TR]
[TR]
[TD="align: right"]23/12/2017[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[/TR]
[TR]
[TD="align: right"]24/12/2017[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[/TR]
[TR]
[TD="align: right"]25/12/2017[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[/TR]
[TR]
[TD="align: right"]26/12/2017[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[/TR]
[TR]
[TD="align: right"]27/12/2017[/TD]
[TD]Customer Service[/TD]
[TD]Customer Service[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[/TR]
[TR]
[TD="align: right"]28/12/2017[/TD]
[TD]Customer Service[/TD]
[TD]Customer Service[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[/TR]
[TR]
[TD="align: right"]29/12/2017[/TD]
[TD]Customer Service[/TD]
[TD]Customer Service[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[/TR]
[TR]
[TD="align: right"]30/12/2017[/TD]
[TD]Customer Service[/TD]
[TD]Customer Service[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[/TR]
[TR]
[TD="align: right"]31/12/2017[/TD]
[TD]Customer Service[/TD]
[TD]Customer Service[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[/TR]
[TR]
[TD="align: right"]1/01/2018[/TD]
[TD]Customer Service[/TD]
[TD]Customer Service[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[/TR]
[TR]
[TD="align: right"]2/01/2018[/TD]
[TD]Customer Service[/TD]
[TD]Customer Service[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[/TR]
[TR]
[TD="align: right"]3/01/2018[/TD]
[TD]Customer Service[/TD]
[TD]Customer Service[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[/TR]
[TR]
[TD="align: right"]4/01/2018[/TD]
[TD]Customer Service[/TD]
[TD]Customer Service[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[/TR]
[TR]
[TD="align: right"]5/01/2018[/TD]
[TD]Customer Service[/TD]
[TD]Customer Service[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[/TR]
[TR]
[TD="align: right"]6/01/2018[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[/TR]
[TR]
[TD="align: right"]7/01/2018[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD]Customer Service[/TD]
[/TR]
[TR]
[TD="align: right"]8/01/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]9/01/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10/01/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11/01/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Accounts[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/01/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Accounts[/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]13/01/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]14/01/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]15/01/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Accounts[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
I believe you want a Gantt chart.

This is how I arranged the data (with correct years), taken from the data you showed. The numbers in columns C and D are the durations of the vacation absences in days.


Book1
ABCD
1Start DateCust ServAccounts
2John27-Dec10
3Mark23-Dec16
4Susan23-Dec21
5Ronald11-Jan5
6Andrea23-Dec16
Sheet1

Select A1:D6 then on the Insert tab, choose "Stacked Bar".

The vertical axis was formatted with "Categories in reverse order".

The horizontal axis was formatted to Bounds – Minimum = 43085; Units – Major = 7, Minor =1.
Minor gridlines were added.

Series 1 was reformatted to have no fill and no line. Gap width for the bars was set to 5%.

The invisible series was removed from the legend.

UJFYld7.png
 
Upvote 0
That looks great, only thing is it seems to only work for one block of holidays. Some people are taking two blocks of holidays e.g. John is also away on the 10th and 11th Jan. I don't really want to have to put his name on the graph twice. Do you have any suggestions on how to tweak your example to fit that?
 
Upvote 0
It can be done as a chart but it's not as simple as my previous example. I arranged the input data like this:


Book1
ABCDEF
1
2NameDepartmentStart1Return1Start2Return2
3JohnCustomer Service27-Dec06-Jan10-Jan12-Jan
4MarkCustomer Service23-Dec08-Jan
5AndreaCustomer Service23-Dec08-Jan
6SusanAccounts23-Dec13-Jan
7RonaldAccounts11-Jan16-Jan
8PatAccounts23-Dec29-Dec06-Jan12-Jan
StackedBar

The data I charted became this:

Book1
HIJKLMNOPQ
1Customer ServiceAccounts
2Calendar StartStart1Duration1Start2Duration2Start1Duration1Start2Duration2
3John16-Dec1110420000
4Mark16-Dec716000000
5Andrea16-Dec716000000
6Susan16-Dec000072100
7Ronald16-Dec000026500
8Pat16-Dec00007686
StackedBar
Cell Formulas
RangeFormula
J3=IF($B3=$J$1, C3-I3, 0)
K3=IF($B3=$J$1, D3-C3, 0)
L3=IF(AND(E3<>"", $B3=$J$1), E3-D3, 0)
M3=IF(AND(F3<>"", $B3=$J$1), F3-E3, 0)
N3=IF($B3=$N$1, C3-I3, 0)
O3=IF($B3=$N$1, D3-C3, 0)
P3=IF(AND(E3<>"", $B3=$N$1), E3-D3, 0)
Q3=IF(AND(F3<>"", $B3=$N$1), F3-E3, 0)

ppi0jER.png


I will suggest copying your original data as you have it set up in your first post, and Paste Special – Transpose, then use conditional formatting to create an in-cells type of chart.

CK3QyEV.png


X1s7AUE.png


When a cell contains "Customer Service", it is given an orange fill. Blue cells are those that contain "Accounts". Text is hidden in those cells by using a custom number format.
Code:
;;;
That's three semicolons with no spaces.

My workbook may be downloaded from https://www.dropbox.com/s/qtoxgx4e4xvjeec/gantt_for_VirtualInsanity.xlsx?dl=0
 
Last edited:
Upvote 0
Thanks so, so much! That worked perfectly - and was so amazing of you to give me the workbook as a download. You really made it so easy, as I was rushing to get that finished for a meeting tomorrow and it would have taken me a long time to nut it all out on my own from the description. Really appreciate your help. Thanks again.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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
Back
Top