Excel Function Questions

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

1. ShingmasteromgOTbangaz4life

Joined:
Apr 13, 2005
Messages:
5,338
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.

2. cxhatchbackNew Member

Joined:
Apr 30, 2006
Messages:
22
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_PreludeThere might not be an "I" in the word "Team", but

Joined:
Mar 14, 2000
Messages:
14,520
2
Location:
Yep - two columns, start and end time, otherwise you're asking for trouble.

Joined:
Apr 13, 2005
Messages:
5,338