Excel 2007 : Creating a time table from 8 to 8

Hi,

I would like to create a workbook with a timetable, similar to the example files attached.

1 Sheet per Date option - Conditional Formatting/Arrow shapes
I want Excel to create one worksheet per date (I can create those manually first if need be) in the given month with column A having the labels in 1 hour intervals from 8:00am to 8:00am the next day.

Then, it needs to setup the data as per the May31st sheet in ExampleTimeSheet. It then needs to draw arrows or otherwise color the cells to mark that someone was present between the In and Out times.


Charting option - Using graphs
The alternative is to use charts.
I have provided an example chart in the TimeTable file on how that chart would look like and be generated and it would need to create 1 chart per date and update the range on the chart accordingly.



Weekends
I don't need the charts/sheets for days which are weekends, i.e. Saturday and Sunday, but keep them in if it makes it easier.

Much appreciate any help and pointers you can offer! Your contribution may save some nursing jobs at the local hospital

[Edit 4]
Cleaned up the thread and the attachments

No longer relevant content


------------------------------

I think automating arrows might be impossible so I'm now considering using Conditional Formatting or the chart (which would be 2 series line chart per day with series lines hidden and vertical lines drawn between each matching data point, as per the attached picture in my opening post)

I've also did some work to make the whole thing maybe a bit easier by calculating length on day1 and length on day2. Database attached.

How that would work:
day1 length add to datetimein to get actual time out on that day (last cell to mark)
day2 length (if applicable) add to dateout+0.333333 (date out 8:00) for the next day to get actual time out on that day

Note that in the file below, I have already made changes to the DateIn so that if TimeIn < 8:00, DateIn was replaced by the previous day's date, so you will not have to make any adjustments to move times from 0:00-8:00 on say 2nd Dec to 1st Dec's date, i.e. already done