Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •