Thread: Creating a Work Schedule (Excel v97 sr2)

1. Creating a Work Schedule (Excel v97 sr2)

I'd like to create a weekly work schedule that I could distribute to the various managers in our hotel. The schedule would ultimately be used to "cost out" the payroll expense of the schedule. Our hours of operation in most areas are 24x7. Most managers have little experience with Excel except for filling-in or typing into templates, so I'd like to keep their input and involvement to a minimum. I've experimented with one or two formats to calculate the daily hours, but many methods seem cumbersome and might lead to confusion among the users. Use of military time, for example, is difficult, especially since some shift times begin at the half-hour.
My knowledge of Excel in general is pretty good (esp. thanks to the responders at this site), but I'm sure that someone has already developed something better. One idea I started with would require the use of 5 adjacent cells on a row just for start time of one day (StartHour, A (or P), Blank, EndHour, A (or P)). A formula (IF) would then check the A's and P's to determine when to add 12 to create military time. This works fine until I realize that I must format a cell to allow for half-hour start or end times (8.5 for eight-thirty formats to 01/08/1900 12:00:00 PM).
Naturally the schedule must include EmplName and Location and perhaps even Task, which I can handle. But what I really need is a starting point for this time-of-day thing. I am most willing to experiment with any and all ideas that readers of this message will share.
Thanks in advance.

2. Re: Creating a Work Schedule (Excel v97 sr2)

A simple way without any coding: You could include 9 columns for entry (for example in A2 - I2):
Month
Day
Year
Start hour
Start min
start am/pm
End hr
End min
end am/pm
Then in J2 calc the start date/time:
=DATE(C2,A2,B2)+(D2+E2/60)/24+(UPPER(LEFT(F2,1))="P")*0.5
Format as something like:
"ddd, mmmm d, yyyy h:mm am/pm"

Then in K2 calc end time (formatted like J1):
=DATE(C2,A2,B2)+(G2+H2/60)/24+(UPPER(LEFT(I2,1))="P")*0.5+(DATE(C2,A2,B2)+(G2 +H2/60)/24+(UPPER(LEFT(I2,1))="P")*0.5<J2)
Steve

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•