Forum Discussion
Excel - calculating hours worked on single cell values
Hi
I'm trying to figure out what I'm sure is a simple excel query. I need to calculate total weekly hours on a staff rota and the rota is formatted with each shift eg 09:00-17:00 in one single cell. Is there a way to sum these hours? Previously it was down manually but means there is room for error. I have seen other examples splitting the shift into two cells (start and end time) but my manager wants to keep the format as is. I've attached an example and would appreciate any feedback.
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.
36 Replies
I am trying to make a spreadsheet work schedule that calculate hours and overtime but the formula is not working, does somebody can help me with that?
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
- I was trying to attached my spreadsheet, but I can not find a place to attached that so you can see
I get "You need access" when I click your link...
- https://docs.google.com/spreadsheets/d/1djNKW9ySHkqCTU1U55ZryvuI-we9BDEAzVPO5cc-6qA/edit?usp=sharing
Try now
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
Hello HansVogelaar,
Hope you're well. Ive seen your work above, and was hoping you can assist in providing with a formula that would work for the format I have in https://docs.google.com/spreadsheets/d/1rhXorSRabhfnQirU-5f8eTpmZQE9zGXE/edit?usp=sharing&ouid=117244844375929167928&rtpof=true&sd=true. Essentially I want to calculate total hours scheduled in column M, this formula should take into account "OFF", "PTO".Thank you in advance for your assistance.
In M12:
=SUM(IFERROR(TIMEVALUE(TEXTAFTER(D12:K12, " - "))-TIMEVALUE(TEXTBEFORE(D12:K12, " - ")), 0))
Apply the custom number format [h]:mm to M12 to allow for total times over 24 hours.
Copy M12 and paste to the cells below where you want a total.
See attached version.
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
HansVogelaar
Hi Sir Hans,
I can't find a way to total the number work hours our employees.
I tried your given formula.Could you attach a sample workbook or make it available through OneDrive, Google Drive or similar?
Or at the least, post a screenshot in which I can see the row numbers and column letters, and one of the formulas you're using.
