Forum Discussion
Solved
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...
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.
I get "You need access" when I click your link...
https://docs.google.com/spreadsheets/d/1djNKW9ySHkqCTU1U55ZryvuI-we9BDEAzVPO5cc-6qA/edit?usp=sharing
Try now
Try now
Thank you. In the first post in this discussion, working times were specified in the form 09:00-17:30, i.e. with a leading zero where needed, and with : as delimiter.
In your workbook, the times are in the form 900-1730, i.e. without leading zero and without delimiter. This requires a slightly different formula - see the attached version.
In the attached example, a lunch break of 30 minutes is subtracted; you can easily change that in the formula if necessary.
