Date & Time graph

pamaga

Board Regular
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?

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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?

Replies
8
Views
248
Replies
1
Views
535
Replies
9
Views
947
Replies
1
Views
923
Replies
7
Views
929

1,220,983
Messages
6,157,194
Members
451,404
Latest member
Probe

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.

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

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