Excel Function Questions

Discussion in 'OT Technology' started by Shingmaster, Apr 26, 2009.

  1. Shingmaster

    Shingmaster omgOTbangaz4life

    Joined:
    Apr 13, 2005
    Messages:
    5,338
    Likes Received:
    0
    Location:
    dirty ass fuckin' jersey
    I'm working on an employee schedule worksheet for my job, what I'm trying to figure out is how to add the total amount of hours used for each day. I'm trying to get Excel to calculate the hour length of each shift (entered in as 915-5, 12-9, 5-11, etc) and add them up at the bottom of the worksheet. I've been messing with different functions but nothing is giving me the results I'm looking for, or anything close to it. I'll grab a screenshot of the specific worksheet so you guys can get a better idea. I need the total amount of hours used for each day to be totaled at the bottom of each respective date. I can pretty much take it from there, any help would be great.. and yes i've googled it.


    http://uploader.ws/upload/200904/untitled_31.jpg
     
  2. cxhatchback

    cxhatchback New Member

    Joined:
    Apr 30, 2006
    Messages:
    22
    Likes Received:
    0
    Assuming one of your "445-11C" entries is in cell A1, try this:

    =VALUE(LEFT(MID(A1,FIND("-",A1,1)+1,5),LEN(MID(A1,FIND("-",A1,1)+1,5))-1)&":00")-VALUE(IF(LEN(LEFT(A1,FIND("-",A1,1)-1))>2,LEFT(LEFT(A1,FIND("-",A1,1)-1),LEN(LEFT(A1,FIND("-",A1,1)-1))-2)&":"&RIGHT(LEFT(A1,FIND("-",A1,1)-1),2)))

    You could really benefit by tracking your time in a more logical and consistent manner and make these formulas a LOT simpler (think: B1-A1). Above, if you ever end a shift at say 11:30, this formula won't work. I just noticed you never do in your above example.

    Also, you're not going to get anything meaningful unless you format the cell as time (use the 13:30 one). Good luck
     
  3. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Yep - two columns, start and end time, otherwise you're asking for trouble.
     
  4. Shingmaster

    Shingmaster omgOTbangaz4life

    Joined:
    Apr 13, 2005
    Messages:
    5,338
    Likes Received:
    0
    Location:
    dirty ass fuckin' jersey
    I figured as much with the two columns, that was the first thing I did when I was messing around with the sheet yesterday, I'm not the one who does the scheduling, I'm just trying to do something for my GM
     

Share This Page