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.
Hi, HansVogelaar please could you help me with this? I tried using your previous formula but it didn’t work it showed 00:00, I now have this formula in but the total isn’t correct?
any help appreciated.
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!