Hi Hui, You should be a busy man. Trying to grab your attention from last 10 days. It would be of great help if you can spare some time and help with logic of the formula & Chart. I have attached the sample file with all 4 months data. Really would appreciate your help...
I have become snowed under and am sorry I won't get to this for a little bit
I am also working on a new Excel project which I want to publish soon.
It will blow your mind. It has blown my mind and I wrote it
The chart
There are 2 sets of stacked column charts
1 set for each vertical axis
The first series in each set is the start time and it is shaded with no fill, but if click below each Colored bar it will be highlighted
The second series, the colored column is the length of time
Click on each series and look at the source range for the series in the Formula Bar
Then follow the formula to see where they came from
RedHotSun
... You used Hui's layout.
The graph would look as Your given data
(as I tried to write too in my sample). ps. #9 Reply uses some formulas which don't work with me;
That's why, for me, there are many not valid values.