Date & Time graph

pamaga

Board Regular
Joined
Jul 3, 2002
Messages
82
Hello all,
I seem to remember an old thread on this subject, but I have searched this forum for a solution and I came up blank... Here's the query.
A sheet contains data for one month, for one employee, as follows.
Column A: Dates from the first to the last of the month, including weekends
Column B: Arrival time
Column C: Lunch pause beginning
Column D: Lunch pause end
Column E: Departure time
Note that weekend days are empty for most employees, but must be present since some people do actually work on weekends.
A graph of the above data is required, as follows.
Days of the month along the X-axis (bottom);
time (7am-9pm) along the Y-axis (side). For
each day, a colour bar (say, blue) should go from the the value in column B to C; the lunch break (colD - colC) should be in a different colour (say, green); finally, another colour bar (again in, say, blue) from time colD to colE.
Did I explain this properly? :)
Thanks for your input.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Mala

MrExcel MVP
Joined
Apr 4, 2002
Messages
627
A custom XY scatter chart can be made to do this.
Try this:
Set up 4 cols of data, col A being the date
For each date have 2 rows (containing that date in col A) and a blank row (total 3 rows for a date)
In col B, put Time1(arrival) and Time2(lunch start)
In col C, put Time2(lunch start) and Time3(lunch end)
In col D, put Time3(lunch end) and Time4(departure)
Build this data for the whole month - for a 31-day month, this will occupy 93 rows and 4 columns.
Create a XY scatter chart and specify 3 data series with the following x and y ranges:
Prelunch > A1:A93, B1:B93
Lunch > A1:A93, C1:C93
Post-lunch>A1:A93, D1:D93
Format the axes and individual series according to your preference.

This is just illustrative of how to make the chart - when you are working with a list (database), you'd need to use code to generate the individual charts for a group of employees.
Hope this helps

P.S.> 'pamaga' - pa, ma and ga are three notes on the musical scale for Indian classical music - is there some association?
 

Forum statistics

Threads
1,144,312
Messages
5,723,654
Members
422,508
Latest member
Lordkit1

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
Top