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.
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!
That looks like Google Sheets, not Excel. I cannot help you with that. sorry.
