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.
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
If you cannot attach a file, upload it to for example Google Drive, Onedrive or Dropbox, and obtain a link that allows us to download the file.
Paste that link into a reply.
- I think I've got it, let me know if works, if you can see the spreadsheet, and thank you so much
I get "Access Denied". You'll have to grant read-only access to others.
- https://docs.google.com/spreadsheets/d/1I-07IACY4S4bPu2u8L5o-PuibTs8jO4c/edit?usp=drive_link&ouid=107982695693900620399&rtpof=true&sd=true