Seamie_
asked on
Stacked Chart in Excel
Hello all,
I have a spreadsheet of the times people logged in and out of a room. Taking two as an example:
Name Date Function Start End Duration
Tom 07/10/2013 Absent 08:04 08:56 00:52
Tom 07/10/2013 Present 08:56 08:59 00:03
Tom 07/10/2013 Absent 08:59 10:02 01:03
Tom 07/10/2013 Present 10:02 10:12 00:10
Tom 07/10/2013 Absent 10:12 11:49 01:37
Tom 07/10/2013 Present 11:49 12:12 00:23
Tom 07/10/2013 Absent 12:12 13:39 01:27
Tom 07/10/2013 Present 13:39 13:50 00:11
Tom 07/10/2013 Absent 13:50 14:14 00:24
Tom 07/10/2013 Present 14:14 15:34 01:20
Tom 08/10/2013 Present 07:52 10:59 03:07
Tom 08/10/2013 Absent 10:59 12:58 01:59
Tom 08/10/2013 Present 12:58 13:24 00:26
Tom 08/10/2013 Absent 13:24 14:13 00:49
Tom 08/10/2013 Present 14:13 15:39 01:26
Tom 09/10/2013 Present 07:58 09:06 01:08
Tom 09/10/2013 Absent 09:06 12:27 03:21
Tom 09/10/2013 Present 12:27 13:26 00:59
Tom 09/10/2013 Absent 13:26 14:03 00:37
Tom 09/10/2013 Present 14:03 15:20 01:17
Tom 10/10/2013 Present 09:01 09:21 00:20
Tom 10/10/2013 Absent 09:21 10:10 00:49
Tom 10/10/2013 Present 10:10 10:12 00:02
Tom 10/10/2013 Absent 10:12 12:16 02:04
Tom 10/10/2013 Present 12:16 12:42 00:26
Tom 10/10/2013 Absent 12:42 13:59 01:17
Tom 10/10/2013 Present 13:59 16:03 02:04
Tom 11/10/2013 Present 07:44 09:37 01:53
Tom 11/10/2013 Absent 09:37 11:42 02:05
Tom 11/10/2013 Present 11:42 12:41 00:59
Tom 11/10/2013 Absent 12:41 14:08 01:27
Tom 11/10/2013 Present 14:08 14:38 00:30
Tom 14/10/2013 Present 11:24 11:25 00:01
Tom 14/10/2013 Absent 11:25 14:16 02:51
Tom 14/10/2013 Present 14:16 15:25 01:09
Pat 07/10/2013 Present 09:05 09:07 00:02
Pat 07/10/2013 Absent 09:07 10:07 01:00
Pat 07/10/2013 Present 10:07 10:14 00:07
Pat 07/10/2013 Absent 10:14 11:14 01:00
Pat 07/10/2013 Present 11:14 12:53 01:39
Pat 07/10/2013 Absent 12:53 13:33 00:40
Pat 07/10/2013 Present 13:33 13:51 00:18
Pat 07/10/2013 Absent 13:51 14:39 00:48
Pat 07/10/2013 Present 14:39 15:33 00:54
Pat 08/10/2013 Present 07:50 09:53 02:03
Pat 08/10/2013 Absent 09:53 11:32 01:39
Pat 08/10/2013 Present 11:32 12:13 00:41
Pat 10/10/2013 Present 09:01 09:32 00:31
Pat 10/10/2013 Absent 09:32 11:29 01:57
Pat 10/10/2013 Present 11:29 11:37 00:08
Pat 10/10/2013 Absent 11:37 14:08 02:31
Pat 10/10/2013 Present 14:08 15:57 01:49
Pat 11/10/2013 Present 08:02 09:56 01:54
Pat 11/10/2013 Absent 09:56 10:26 00:30
Pat 11/10/2013 Present 10:26 10:52 00:26
Pat 11/10/2013 Absent 10:52 11:51 00:59
Pat 11/10/2013 Present 11:51 12:38 00:47
Pat 11/10/2013 Absent 12:38 14:53 02:15
Pat 11/10/2013 Present 14:53 15:52 00:59
Pat 14/10/2013 Present 08:40 10:13 01:33
Pat 15/10/2013 Absent 10:13 11:43 01:30
Pat 16/10/2013 Present 11:43 13:02 01:19
Pat 17/10/2013 Absent 13:02 14:12 01:10
Pat 18/10/2013 Present 14:12 15:35 01:23
I'd like to present these in a horizontal bar chart such that:
[Date]
Tom | PPPPAAPPPAAAAAPPAAAAAA....
|
|
Pat |PAAAPPPAPPPAAAAPPAAAPPAAA. .
<Time Start> -----------------------<Ti me End>
Where 'P' is the colour of the bar representing 'Present' and 'A' is the colour of the bar representing 'Absent'.
I've been at this trying as many permutations in Excel as possible without success. I've googled it and looked for solutions here without success. Can anyone advise, please?
Regards,
Seamie
I have a spreadsheet of the times people logged in and out of a room. Taking two as an example:
Name Date Function Start End Duration
Tom 07/10/2013 Absent 08:04 08:56 00:52
Tom 07/10/2013 Present 08:56 08:59 00:03
Tom 07/10/2013 Absent 08:59 10:02 01:03
Tom 07/10/2013 Present 10:02 10:12 00:10
Tom 07/10/2013 Absent 10:12 11:49 01:37
Tom 07/10/2013 Present 11:49 12:12 00:23
Tom 07/10/2013 Absent 12:12 13:39 01:27
Tom 07/10/2013 Present 13:39 13:50 00:11
Tom 07/10/2013 Absent 13:50 14:14 00:24
Tom 07/10/2013 Present 14:14 15:34 01:20
Tom 08/10/2013 Present 07:52 10:59 03:07
Tom 08/10/2013 Absent 10:59 12:58 01:59
Tom 08/10/2013 Present 12:58 13:24 00:26
Tom 08/10/2013 Absent 13:24 14:13 00:49
Tom 08/10/2013 Present 14:13 15:39 01:26
Tom 09/10/2013 Present 07:58 09:06 01:08
Tom 09/10/2013 Absent 09:06 12:27 03:21
Tom 09/10/2013 Present 12:27 13:26 00:59
Tom 09/10/2013 Absent 13:26 14:03 00:37
Tom 09/10/2013 Present 14:03 15:20 01:17
Tom 10/10/2013 Present 09:01 09:21 00:20
Tom 10/10/2013 Absent 09:21 10:10 00:49
Tom 10/10/2013 Present 10:10 10:12 00:02
Tom 10/10/2013 Absent 10:12 12:16 02:04
Tom 10/10/2013 Present 12:16 12:42 00:26
Tom 10/10/2013 Absent 12:42 13:59 01:17
Tom 10/10/2013 Present 13:59 16:03 02:04
Tom 11/10/2013 Present 07:44 09:37 01:53
Tom 11/10/2013 Absent 09:37 11:42 02:05
Tom 11/10/2013 Present 11:42 12:41 00:59
Tom 11/10/2013 Absent 12:41 14:08 01:27
Tom 11/10/2013 Present 14:08 14:38 00:30
Tom 14/10/2013 Present 11:24 11:25 00:01
Tom 14/10/2013 Absent 11:25 14:16 02:51
Tom 14/10/2013 Present 14:16 15:25 01:09
Pat 07/10/2013 Present 09:05 09:07 00:02
Pat 07/10/2013 Absent 09:07 10:07 01:00
Pat 07/10/2013 Present 10:07 10:14 00:07
Pat 07/10/2013 Absent 10:14 11:14 01:00
Pat 07/10/2013 Present 11:14 12:53 01:39
Pat 07/10/2013 Absent 12:53 13:33 00:40
Pat 07/10/2013 Present 13:33 13:51 00:18
Pat 07/10/2013 Absent 13:51 14:39 00:48
Pat 07/10/2013 Present 14:39 15:33 00:54
Pat 08/10/2013 Present 07:50 09:53 02:03
Pat 08/10/2013 Absent 09:53 11:32 01:39
Pat 08/10/2013 Present 11:32 12:13 00:41
Pat 10/10/2013 Present 09:01 09:32 00:31
Pat 10/10/2013 Absent 09:32 11:29 01:57
Pat 10/10/2013 Present 11:29 11:37 00:08
Pat 10/10/2013 Absent 11:37 14:08 02:31
Pat 10/10/2013 Present 14:08 15:57 01:49
Pat 11/10/2013 Present 08:02 09:56 01:54
Pat 11/10/2013 Absent 09:56 10:26 00:30
Pat 11/10/2013 Present 10:26 10:52 00:26
Pat 11/10/2013 Absent 10:52 11:51 00:59
Pat 11/10/2013 Present 11:51 12:38 00:47
Pat 11/10/2013 Absent 12:38 14:53 02:15
Pat 11/10/2013 Present 14:53 15:52 00:59
Pat 14/10/2013 Present 08:40 10:13 01:33
Pat 15/10/2013 Absent 10:13 11:43 01:30
Pat 16/10/2013 Present 11:43 13:02 01:19
Pat 17/10/2013 Absent 13:02 14:12 01:10
Pat 18/10/2013 Present 14:12 15:35 01:23
I'd like to present these in a horizontal bar chart such that:
[Date]
Tom | PPPPAAPPPAAAAAPPAAAAAA....
|
|
Pat |PAAAPPPAPPPAAAAPPAAAPPAAA.
<Time Start> -----------------------<Ti
Where 'P' is the colour of the bar representing 'Present' and 'A' is the colour of the bar representing 'Absent'.
I've been at this trying as many permutations in Excel as possible without success. I've googled it and looked for solutions here without success. Can anyone advise, please?
Regards,
Seamie
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm looking forward to parsing out the solution.
Many thanks.
Seamie