Excel Help....

Discussion in 'Computers' started by Andrew S, Jun 22, 2005.

  1. Andrew S

    Andrew S Stunt Coordinator

    Joined:
    Sep 30, 2001
    Messages:
    214
    Likes Received:
    0
    Trophy Points:
    0
    I'm working on a spreadsheet for a work schedule and want to know if there's a way to calculate total number of hours for each employee per week when it's set up something like this:
    Monday Tuesday Wed Thurs Friday
    Pete: 9-2, 7-3, 7-3, OFF, 9-5,
    etc..

    At first I used:
    =(COUNTIF(C1895:I1895, "8-4")+COUNTIF(C1895:I1895, "7-3")+COUNTIF(C1895:I1895, "6-2")+COUNTIF(C1895:I1895, "9-5"))*8

    ... but would like it to somehow interpret a non-8-hour shift if it occurs, like 8-3 for example.

    If anyone has any tips they would be much appreciated.
    Andrew
     
  2. MarkMel

    MarkMel Screenwriter

    Joined:
    Nov 19, 2003
    Messages:
    1,875
    Likes Received:
    116
    Trophy Points:
    1,610
    The best way to do this would be to have a start time and an end time, a seperate cell for each then calculate the time in a third cell. Then sum all of the third cells.

    Go to this website for good hints.

    www.tek-tips.com
     
  3. Joseph DeMartino

    Joseph DeMartino Lead Actor

    Joined:
    Jun 30, 1997
    Messages:
    8,311
    Likes Received:
    13
    Trophy Points:
    5,610
    Location:
    Florida
    Real Name:
    Joseph DeMartino
    Andrew I can send you a personal timesheet file that I use to keep track of my own hours at work. You can see the structure and copy the formulas. It uses separate start and stop time cells (4 of them, actually, since I have to deduct the lunch hour) and requires that the time be entered in 24 hour clock format. Works like a charm.

    Send me an e-mail and I'll reply with a copy of the file.

    Regards,

    Joe
     
  4. John_Bonner

    John_Bonner Supporting Actor

    Joined:
    Oct 25, 2000
    Messages:
    664
    Likes Received:
    0
    Trophy Points:
    0
    I would change the format of the cells to Time. (FORMAT, CELLS, TIME). Then have a column for Start time and a column for End time. You would enter 9-2 as 9:00 AM in the Start time column and 2:00 PM in the End time column. A third column would subtract the two and give you Time Worked as 5:00 (5 hours).
     
  5. Joseph DeMartino

    Joseph DeMartino Lead Actor

    Joined:
    Jun 30, 1997
    Messages:
    8,311
    Likes Received:
    13
    Trophy Points:
    5,610
    Location:
    Florida
    Real Name:
    Joseph DeMartino


    You'll want to format that third column as "number" with maybe one or two decimal places, rather than time, or the system will return a time-of-day value rather than total hours. (If I format the total cell as "time" on the spreadsheet I mentioned above an 8 hour day shows up as "12:00 AM". [​IMG])

    Regards,

    Joe
     
  6. Joseph DeMartino

    Joseph DeMartino Lead Actor

    Joined:
    Jun 30, 1997
    Messages:
    8,311
    Likes Received:
    13
    Trophy Points:
    5,610
    Location:
    Florida
    Real Name:
    Joseph DeMartino
    For anyone interested in a solution to this problem, here's how my timesheet spreadsheet handles the calcuations:

    Because we work a 9 hour day with an hour off for lunch, we need to enter time in for the morning, time out for luch, time in for the afternoon and then time out for the day. We're on a bi-weekly pay schedule and payroll weeks from start on Fridays. For that reason I use a formula to display the day of the week, so that if I screw up entering a date the mistake is immediately evident.

    In any case, the basic spreadsheet uses 7 colums labelled and formatted as follows:

    A - Day - Forumla based on date entered in column B. Displays day spelled-out, "Sunday" through "Saturday" (Format: Number, Custom, "dddd")

    B - Date - Diplays as "December 16, 2005".
    (Format: Number, Custom, "mmmm d, yyyy"

    C - In - "8:00 AM" Input in 24 hour time format.
    (Format: Number, Time, "1:30 PM")

    D - Lunch, Out. Same as "C"

    E - Lunch In, Same as "C"

    F - Out. Same as "C". Note: for shifts that extened past midnight (24:00), simply add the extra time to "24" If an employee clocks out at 1:00 AM enter "25:00", 2:30 AM enter "26:30", etc. The display and the calculations will both be correct.

    G - Contains the formula for caculating hours worked:
    =(((D8-C8)*24)+((F8-E8)*24))
    (Example is from line 8 of the finished form, the first on which hours worked are recorded.)
    (Format: Number, Number, 2 decimal places.)

    Regards,

    Joe
     

Share This Page