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.
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.