Time sheet as bars with breaks as Excel chart

[CyrusTexas]

Hello, I am trying to create an Excel chart that functions as a time sheet (names on the y-axis and time of day on the x-axis) and I am having a lot of trouble doing this! This is not an problem when there are no time-off breaks during working hours but incorporating those gaps for each person in the chart is what is proving to be very challenging. I hope the following example will clarify things further:

As an example, for employee John who works, say, from 9A until 12 noon, takes a 15 min break, then restarts work at 1215P until a second break at 2P and works a final shift from 215P until 500P, I would like to show a single line for John from 9A until 5P but with two breaks in that line accounting for the two 15 min breaks at noon and 2P.

Would someone please help? Thank you!

 

[GraH]

Hi, are you after something that resembles this?

To show the gaps, you need a time series (per interval of 15') and then plot the "presence" against that interval.

 

[GraH]

With a bit of set-up it can look like so

 

[CyrusTexas]

Thank you GraH! Yes, that is exactly what I am looking for with a time series chart but I cannot seem to get it to show up like that. Would you please show me the format of your input data for John, Mike, and Bert in the second chart? I do appreciate your help!

 

[GraH]

I can do that tomorrow. On my smartphone now without Excel. Kind of difficult.
Attempt to explain:
It is a xy scatter, first value is a custom format "name" where each name is actualy a number 1,2,3. Then an emty row. The x-values are each 15 minutes from start until end.
The Axis is defined with custom values. 1/24 is 1 hour and I have defined the intervals as 1/96 or 15 minutes.

 

[GraH]

CyrusTexas, as promised yesterday.

mr Excel forum-solving rank on 2 columns.xlsx
	C	D	E	F
2		John	Mike	Bert
3	6:00	John	Mike	Bert
4	6:15
5	9:00	1		3
6	9:15	1		3
7	9:30	1		3
8	9:45	1		3
9	10:00	1		3
10	10:15	1		3
11	10:30	1		3
12	10:45	1		3
13	11:00	1	2	3
14	11:15	1	2	3
15	11:30	1	2	3
16	11:45	1	2	3
17	12:00	1	2	3
18	12:15		2	3
19	12:30	1	2	3
20	12:45	1	2	3
21	13:00	1	2	3
22	13:15	1	2	3
23	13:30	1	2	3
24	13:45	1	2	3
25	14:00	1	2	3
26	14:15
27	14:30	1
28	14:45	1
29	15:00	1
30	15:15	1	2
31	15:30	1	2
32	15:45	1	2
33	16:00	1	2
34	16:15	1	2
35	16:30	1	2
36	16:45	1	2
37	17:00	1	2
38	17:15	1	2
Sheet4

Cell Formulas
Range	Formula
C4,C6:C38	C4	=C3+TIME(0,15,0)

Custom format of the cells at "6:00", though it is possible a simple label can also do.

Label as Value From Cells option

X-axis set-up

 

[CyrusTexas]

Hello GraH, thank you again for taking the time and effort to help me out. It is truly appreciated.

 

[GraH]

You're welcome. Nice to see the appreciation so thanks for the feedback. And... since you liked, then hit the likes

 

[CyrusTexas]

Hello GraH, I really did not want to bother you again but it is taking me way too long to figure out the last step for my time-sheet chart and it was more efficient to swallow my pride and request your assistance again!

I thought we could attach files here but it turns out we cannot do so. It would be very convenient if you could work off the attached image or what you had because despite your instructions and countless online searches, I cannot seem to get my y-axis labels to represent the staff names like you were able to do. I realize that is a lot of work but even a brief stepwise protocol would help a lot. Thank you!

 

[GraH]

Hi,
I think I spot where you are in trouble.

The names need to be datapoints. Where you have put the names type in the numbers 1,2,3 and 4. They will show up on the chart.

Then to add the names either label only the first datapoint of all the series or custom format the values of the first datapoints. So 1 is formatted as "John", 2 as "James", and so on.