Graveyard Work Schedule

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a work schedule that will graph all employees for an
entire week. I'm not sure how to set up the date portion of the spreadsheet
to get the graph.
In column A I place the employee names.
In Column B I have the start time for Monday.
In Column C I have the stop time for Monday.
...
...
...
In column O I have the start time for Sunday
In column P I have the stop time for Sunday.

My problem is there are a few employees that start at say 8PM on Monday and
end their shift at 7AM on Tuesday.

I have tried many different methods and all to no avail?

Any help would be greatly appreciated.

Thanks,

frustated El Bee!
 
Here are some ideas, maybe there will be something you haven't tried. Each
bullet is separate from the others:
- Instead of days of the week as your column headers use shift#1 start,
shift#1 end, shift #2 start, shift#2 end, etc. Then format the cells using
the custom setting: mm/dd/yyyy hh:mm. You'd have to then enter all your data
that way, with the date included.
- When somone starts a night shift always put their end time under that same
day even if they really end on the next day. When you key in the end time,
add 24 hours (in your head) to the real time. For example, for someone
ending at 6 am on a Tuesday in the "Monday End" column you'd put "30:00" as
the end time, which will show up as 6 am but will allow you to correctly
subtract the start time, let's say it was 8 pm which is entered as "20:00",
to get the real shift length in hours.
- Make your formulas sensitive by using some IF statements, like
"=Endtime-Starttime+IF(endtime<starttime,24,0)". This formula calculates
shift length, then adds 24 hours if the endtime (e.g., 6 AM) is less than the
start time (e.g., 8 PM) to give the real shift length. For a weekly total
you'd have to string seven of these formulas together in one cell, or
calculate each day separately then sum across those subtotals.

HTH
 
Back
Top