I work in retail and am currently creating a spreadsheet for Rosters. The user enters StartTime / EndTime for an employees shift. This data is then collated into a table like this:
Monday
Employee1 09:00 17:30
Employee2 09:00 17:30
Employee3 10:00 16:00
Employee4 09:00 15:00
etc..
I then calculate how many staff are working at a particular time:
Monday
00:00 #N/A
...
08:00 #N/A
09:00 3
10:00 4
11:00 4
12:00 4
13:00 4
14:00 4
15:00 3
16:00 2
17:00 2
18:00 #N/A
...
24:00 #N/A
This all works perfectly fine.
I am then trying to chart this data (with time along the x-axis and number of staff on the y-axis). I avoid charting the 0 values by using a table containing #N/A values.
The issue with the graph is that the time axis always display a full 24 hour period, 00:00 thru to 23:59 (the table needs to allow for a whole 24 hour period as some outlets could be trading all day). For many stores,
this leaves the data looking somewhat 'lonely' in the middle of the chart with large gaps between 00:00 to 09:00 and 18:00 to 23:59.
I've tried to create a table where the time value is #N/A if the number of staff is #N/A (0). However, as excel is using the time for the x-axis it seems to insist on charting the #N/As on the axis regardless.
I need some way to make the 'Source Data' dynamic such that it only encompasses the none #N/A values (ie 09:00 to 17:00 in the above example).
Any help would be greatly appreciated.
Monday
Employee1 09:00 17:30
Employee2 09:00 17:30
Employee3 10:00 16:00
Employee4 09:00 15:00
etc..
I then calculate how many staff are working at a particular time:
Monday
00:00 #N/A
...
08:00 #N/A
09:00 3
10:00 4
11:00 4
12:00 4
13:00 4
14:00 4
15:00 3
16:00 2
17:00 2
18:00 #N/A
...
24:00 #N/A
This all works perfectly fine.
I am then trying to chart this data (with time along the x-axis and number of staff on the y-axis). I avoid charting the 0 values by using a table containing #N/A values.
The issue with the graph is that the time axis always display a full 24 hour period, 00:00 thru to 23:59 (the table needs to allow for a whole 24 hour period as some outlets could be trading all day). For many stores,
this leaves the data looking somewhat 'lonely' in the middle of the chart with large gaps between 00:00 to 09:00 and 18:00 to 23:59.
I've tried to create a table where the time value is #N/A if the number of staff is #N/A (0). However, as excel is using the time for the x-axis it seems to insist on charting the #N/As on the axis regardless.
I need some way to make the 'Source Data' dynamic such that it only encompasses the none #N/A values (ie 09:00 to 17:00 in the above example).
Any help would be greatly appreciated.