bar chart with duration of an event against time of day

[marilyn]

I was given data and instructed to provide a bar chart showing duration of an event against hours of the day. Each bar should be colored to match the location of the event. It was being completed manually by the prior employee. I attached a sample of the data (anonymized) as well as what the chart should look like. Thank you

 

[vletm]

... just noticed this
... do You still miss this?
I copied that 'Dec2016' data and did something...
There is [Do It]-button, press it!
> Ideas ... Questions?

 

[vletm]

@marilyn
... and there are rules the way it works
... and if 'break' those rules .. it will work as it works

 

[vletm]

... few modifications and
hint:
If need to see other months data,
1) delete old data from Sheet1 ( not 1st row )
2) copy new data to Sheet1 as original data!
... it seems that dates and times has saved as text!
3) if over midnight data would like to see
... just let me know - okay?

 

[marilyn]

... few modifications and
hint:
If need to see other months data,
1) delete old data from Sheet1 ( not 1st row )
2) copy new data to Sheet1 as original data!
... it seems that dates and times has saved as text!
3) if over midnight data would like to see
... just let me know - okay?

wow, this is amazing. I wish I knew how to do this -
I have been traveling so my apologies for not responding sooner.
I will need to show data over midnight.

I also have another request

Is it possible to add the initials as shown on the chart on Jan 3?

thank you very very much.

 

[vletm]

@marilyn
... I gotta do that 'overnight' later,
but the initials could show like this file.
If shorter than 30min durations then ... challenge!
Those takes two cells!
If really needs to turn initials then
... smaller letters or those needs '45min'.

 

[vletm]

@marilyn
with 'over midnight' version.
1) There are ... something ... with Your formulas,
but no matter for 'bar chart' (not use those values).
2) That 15min steps would make some overlaps,
but of course those could do ex 1min steps too.
> Ideas ... questions?

 

[vletm]

@marilyn
ver1 - few modifications
ver2 - without 15min steps

 

[marilyn]

Thank you immensely for this, it is amazing. In version 1, how do I delete the red mark (comments/notes) that are on every bar?

 

[vletm]

The quickest way is:
fromto
Those comments tells details of bars.

 

[marilyn]

Now I have a problem - the data was sent to me in a different format and that seems to be causing a 'type mismatch'.

 

[vletm]

... Houston ... we have a problem ... Yes!
Data have to be in correct/same format!
... now, it's a little slower
... because, it have to solve those formats.

 

[marilyn]

Thank you, I noticed some of the time spans are not reflected in the chart correctly. I highlighted them. Thanks again

 

[vletm]

Yes ... hmm ... Yes
Middays are something ...
There are some challenges to 'understand' some times...
I added one second to every timestamp and
it seems to look better - or how?

There were marked one overnight too - isn't that look correct?
The last part (after midnight) is next days early-early morning.

 

[marilyn]

vletm, I hope you can help me with this! Our data now comes from a new source and the formatting for time has changed, I think that's the issue. But also, we have 2 new rooms that have been added, 105 and 106. The attached file is the one above from Sep 2017, but the data is new on the third tab, and I added 2 new rooms on the 1st tab. I have not clicked "Do it" yet, so the macro has not been run on this one

 

[vletm]

Here is next sample version.
More Places have to add manually.
NMs will come automatic.
Press [ ReLoad ] to ... reload data from InPut-sheet.
There are still something which should fix ... later.
PS. 24hrs is short time ... after few years.