Forum Discussion
Excel - calculating hours worked on single cell values
In I3 as an array formula, confirmed with Ctrl+Shift+Enter:
=SUM(IFERROR(MOD(TIMEVALUE(RIGHT(B3:H3,5))-TIMEVALUE(LEFT(B3:H3,5)),1),0))Apply the custom number format [h]:mm to I3, then fill down.
HansVogelaar Hi There, you've been so helpful with everyone else. i'm wondering if you can work your magic again. I also have a schedule with 32 employees and my ownership is steadfast on keeping the schedule format as it is. however, i need to total each employees hours at the end of the week. here is a completed one so you know what my end result should be. i'd just much rather not do this by hand any longer. THANK YOU!!!
| JULY | 15 | 16 | 17 | 18 | 19 | 20 | 21 | ||
| Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | Hours | Actual | |
| MANAGERS | |||||||||
| STEVE W. | 6pm-12am | 6pm-12am | 6pm-12am | 18.00 | |||||
| BRIAN | 3pm-10pm | 11am-4pm | 3pm-12am | 3pm-12am | 3pm-12am | 3pm-12am | 48.00 | ||
| JIMMY | 9am-3pm | 9am-3pm | 12pm-4pm | 9am-3pm | 12pm-3pm | 9am-3pm | 31.00 | ||
| VINCE | GP | 3pm-12am | GP | GP | 10am-6pm | 10am-5pm | 24.00 | ||
| ZACK | 6pm-12am | 4pm-12am | 9am-4pm | 9am-3pm | 4pm-10pm | 12pm-5pm | 40.00 | ||
| ANTHONY | 4pm-12am | 9am-4pm | 4pm-12am | 4pm-12am | 9am-5pm | 4pm-12am | 47.00 | ||
| GERALD | 4pm-12am | 4pm-6pm | 4pm-10pm | 11am-5pm | 5pm-11pm | 5pm-12pm | 35.00 | ||
| NASH/AMY | |||||||||
| WAITSTAFF | Pool | Pool | Pool | Pool | Pool | Pool | |||
| MALIK | 6pm-11pm | 4pm-11pm | 2pm-11pm | 12pm-11pm | 4pm-11pm | 40.00 | |||
| HOSS | 9:30-4pm | 9:30-4pm | 9:30-4pm | 9:30-4pm | 9:30-3pm | 29.00 | |||
| SANTI | 12pm-11pm | 3pm-11pm | 9:30-4pm | 2pm-11pm | 6pm-11pm | 6pm-11pm | 44.00 | ||
| KELLY | 4pm-11pm | 12pm-11pm | 3pm-11pm | 3pm-11pm | 34.00 | ||||
| LINDSEY | 1pm-11pm | 5pm-11pm | 16.00 | ||||||
| ALYSSA | 12pm-11pm | 5pm-11pm | 17 | ||||||
| DEXTER | 4pm-11pm | 3pm-11pm | 4pm-11pm | 22.00 | |||||
| SIERRA | 6pm-11pm | 3pm-11pm | 6pm-11pm | 12pm-9pm | 27.00 | ||||
| BARTENDERS | Pool | Pool | Pool | Pool | Pool | Pool | Pool | ||
| BYRON | 9am-3pm | 9am-3pm | 9am-2pm | 9am-3pm | 9am-3pm | 9am-3pm | 35.00 | ||
| MICHAEL | 9:30-3/4-11pm | 4pm-11pm | 2pm-11pm | 2pm-11pm | 37.00 | ||||
| VICTORIA | 2pm-11pm | 4pm-11pm | 16.00 | ||||||
| LARRY | 4pm-11pm | 2pm-11pm | 4pm-11pm | 23.00 | |||||
| IVAN | S 9:30-4pm | 4pm-11pm | 2pm-11pm | 4pm-11pm | S 4pm-11pm | 2pm-8pm | 42.00 | ||
| TONY | S 6pm-11pm | S 6pm-11pm | 3pm-11pm | S 5pm-11pm | S 12pm-11pm | S 3pm-9pm | 41.00 | ||
| BARBACKS | |||||||||
| ALYSSA | 4:30pm-12am | 4:30pm-12am | 4:30pm-12am | 4:30pm-12am | 30.00 | ||||
| JEREMY | 4:00pm-12am | 7.00 | |||||||
| KITCHEN | |||||||||
| MATT | gp | gp | 3pm-11pm | gp | 10am-4pm | gp | 14.00 | ||
| MOE | 10am-4pm | 10am-4pm | 10am-4pm | 2pm-10pm | 10am-4pm | 32.00 | |||
| CARL | 12pm-11pm | 1pm-11pm | 10am-4pm | 1pm-10pm | 1pm-10pm | 1pm-10pm | 54.00 | ||
| KEVIN | 10am-4pm | 10am-11pm | 10am-3pm | 4pm-10pm | 30.00 | ||||
| JEFF | 1pm-11pm | 10am-4pm | 2pm-11pm | 1am-10pm | 12pm-10pm | 10am-10pm | 54 | ||
| JERMAINE | 3pm-11pm | 2pm-11pm | 10am-4pm | 12am-10pm | 3pm-10pm | 40 | |||
| STEVON | 1pm-11pm | 2pm-11pm | 2pm-10pm | 2pm-10pm | 10am-4pm | 41 | |||
| DISHWASHER | |||||||||
| OTIS | 5pm-12am | 5pm-11pm | 13.00 | ||||||
| HENRY | 2pm-12am | 5pm-12am | 17.00 | ||||||
| DERRICK | 10am-5pm | 5pm-12am | 5pm-12am | 10:30am-3pm | 25.50 | ||||
| SAM | 10am-3pm | 3pm-12am | 12pm-12am | 26.00 | |||||
| CARLOS | 9am-3pm | 9am-3pm | 9am-3pm | 9am-3pm | 9am-3pm | 9am-3pm | 36.00 | ||
| MICHELLE | 6pm-12am | 6pm-12am | 12.00 | ||||||
| DAMON | 5pm-12am | 4pm-12am | 4pm-12am | 10:30am-5pm | Busser5:00pm-12am | Busser5:00pm-12am | 29.50 | 14.00 | |
| JAMES | 10am-4pm | 10am-3pm | 3pm-8pm | 16.00 |
In J4:
=LET(t, SUBSTITUTE(SUBSTITUTE(B4:H4, "am", " am"), "pm", " pm"), SUM(IFERROR(MOD(TIMEVALUE(TEXTAFTER(t, "-"))-TIMEVALUE(TEXTBEFORE(t, "-")), 1), 0)))*24
Format as General, then copy to the other cells where you need a total.
See the attached demo workbook.
HansVogelaar that's AMAZING!!! You're a miracle worker! Thank you!