Jan 13 2021 10:56 AM
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.
Jan 13 2021 11:53 AM
SolutionIn 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.
Jan 13 2021 02:45 PM
Thanks Hans for the quick response I will try that formula
Mar 24 2021 02:11 PM
Mar 24 2021 02:23 PM
I don't see an attachment?
Mar 24 2021 02:36 PM
Mar 24 2021 03:03 PM
Mar 26 2021 03:13 AM
Mar 03 2022 08:13 PM
@Hans Vogelaar
Hi Sir Hans,
I can't find a way to total the number work hours our employees.
I tried your given formula.
Mar 04 2022 04:25 AM
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.
Jun 07 2022 01:07 AM
Jun 07 2022 04:08 AM - edited Jun 07 2022 04:23 AM
@socialrusty1987 This is the formula I came up with, it is pretty long so I have attached the file where I have the steps to what I did as well.
=IFERROR(IF(((TIME(MID(D5,7,2),RIGHT(D5,2),0))-(TIME(LEFT(D5,2),MID(D5,4,2),0)))<0,(1+(TIME(MID(D5,7,2),RIGHT(D5,2),0))-(TIME(LEFT(D5,2),MID(D5,4,2),0))),((TIME(MID(D5,7,2),RIGHT(D5,2),0))-(TIME(LEFT(D5,2),MID(D5,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D6,7,2),RIGHT(D6,2),0))-(TIME(LEFT(D6,2),MID(D6,4,2),0)))<0,(1+(TIME(MID(D6,7,2),RIGHT(D6,2),0))-(TIME(LEFT(D6,2),MID(D6,4,2),0))),((TIME(MID(D6,7,2),RIGHT(D6,2),0))-(TIME(LEFT(D6,2),MID(D6,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D7,7,2),RIGHT(D7,2),0))-(TIME(LEFT(D7,2),MID(D7,4,2),0)))<0,(1+(TIME(MID(D7,7,2),RIGHT(D7,2),0))-(TIME(LEFT(D7,2),MID(D7,4,2),0))),((TIME(MID(D7,7,2),RIGHT(D7,2),0))-(TIME(LEFT(D7,2),MID(D7,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D8,7,2),RIGHT(D8,2),0))-(TIME(LEFT(D8,2),MID(D8,4,2),0)))<0,(1+(TIME(MID(D8,7,2),RIGHT(D8,2),0))-(TIME(LEFT(D8,2),MID(D8,4,2),0))),((TIME(MID(D8,7,2),RIGHT(D8,2),0))-(TIME(LEFT(D8,2),MID(D8,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D9,7,2),RIGHT(D9,2),0))-(TIME(LEFT(D9,2),MID(D9,4,2),0)))<0,(1+(TIME(MID(D9,7,2),RIGHT(D9,2),0))-(TIME(LEFT(D9,2),MID(D9,4,2),0))),((TIME(MID(D9,7,2),RIGHT(D9,2),0))-(TIME(LEFT(D9,2),MID(D9,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D10,7,2),RIGHT(D10,2),0))-(TIME(LEFT(D10,2),MID(D10,4,2),0)))<0,(1+(TIME(MID(D10,7,2),RIGHT(D10,2),0))-(TIME(LEFT(D10,2),MID(D10,4,2),0))),((TIME(MID(D10,7,2),RIGHT(D10,2),0))-(TIME(LEFT(D10,2),MID(D10,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D11,7,2),RIGHT(D11,2),0))-(TIME(LEFT(D11,2),MID(D11,4,2),0)))<0,(1+(TIME(MID(D11,7,2),RIGHT(D11,2),0))-(TIME(LEFT(D11,2),MID(D11,4,2),0))),((TIME(MID(D11,7,2),RIGHT(D11,2),0))-(TIME(LEFT(D11,2),MID(D11,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D12,7,2),RIGHT(D12,2),0))-(TIME(LEFT(D12,2),MID(D12,4,2),0)))<0,(1+(TIME(MID(D12,7,2),RIGHT(D12,2),0))-(TIME(LEFT(D12,2),MID(D12,4,2),0))),((TIME(MID(D12,7,2),RIGHT(D12,2),0))-(TIME(LEFT(D12,2),MID(D12,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D13,7,2),RIGHT(D13,2),0))-(TIME(LEFT(D13,2),MID(D13,4,2),0)))<0,(1+(TIME(MID(D13,7,2),RIGHT(D13,2),0))-(TIME(LEFT(D13,2),MID(D13,4,2),0))),((TIME(MID(D13,7,2),RIGHT(D13,2),0))-(TIME(LEFT(D13,2),MID(D13,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D14,7,2),RIGHT(D14,2),0))-(TIME(LEFT(D14,2),MID(D14,4,2),0)))<0,(1+(TIME(MID(D14,7,2),RIGHT(D14,2),0))-(TIME(LEFT(D14,2),MID(D14,4,2),0))),((TIME(MID(D14,7,2),RIGHT(D14,2),0))-(TIME(LEFT(D14,2),MID(D14,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D15,7,2),RIGHT(D15,2),0))-(TIME(LEFT(D15,2),MID(D15,4,2),0)))<0,(1+(TIME(MID(D15,7,2),RIGHT(D15,2),0))-(TIME(LEFT(D15,2),MID(D15,4,2),0))),((TIME(MID(D15,7,2),RIGHT(D15,2),0))-(TIME(LEFT(D15,2),MID(D15,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D16,7,2),RIGHT(D16,2),0))-(TIME(LEFT(D16,2),MID(D16,4,2),0)))<0,(1+(TIME(MID(D16,7,2),RIGHT(D16,2),0))-(TIME(LEFT(D16,2),MID(D16,4,2),0))),((TIME(MID(D16,7,2),RIGHT(D16,2),0))-(TIME(LEFT(D16,2),MID(D16,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D17,7,2),RIGHT(D17,2),0))-(TIME(LEFT(D17,2),MID(D17,4,2),0)))<0,(1+(TIME(MID(D17,7,2),RIGHT(D17,2),0))-(TIME(LEFT(D17,2),MID(D17,4,2),0))),((TIME(MID(D17,7,2),RIGHT(D17,2),0))-(TIME(LEFT(D17,2),MID(D17,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D18,7,2),RIGHT(D18,2),0))-(TIME(LEFT(D18,2),MID(D18,4,2),0)))<0,(1+(TIME(MID(D18,7,2),RIGHT(D18,2),0))-(TIME(LEFT(D18,2),MID(D18,4,2),0))),((TIME(MID(D18,7,2),RIGHT(D18,2),0))-(TIME(LEFT(D18,2),MID(D18,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D19,7,2),RIGHT(D19,2),0))-(TIME(LEFT(D19,2),MID(D19,4,2),0)))<0,(1+(TIME(MID(D19,7,2),RIGHT(D19,2),0))-(TIME(LEFT(D19,2),MID(D19,4,2),0))),((TIME(MID(D19,7,2),RIGHT(D19,2),0))-(TIME(LEFT(D19,2),MID(D19,4,2),0))))*24,0)
Hopefully this is what you were looking for
Jun 08 2022 01:46 AM
@V-GEe7 that’s perfect thank you it’s saved me hours so far!!
Sep 18 2022 02:47 AM
Could you help me? I have tried but it just isn't working. I'm using excel but it wouldn't let me share can you use make it excel and send the formatted
https://docs.google.com/spreadsheets/d/1djNKW9ySHkqCTU1U55ZryvuI-we9BDEAzVPO5cc-6qA/edit?usp=sharing
Sep 18 2022 03:23 AM
I get "You need access" when I click your link...
Sep 19 2022 02:38 AM
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.
Jan 18 2023 04:13 PM
Jan 19 2023 03:36 AM
In the attached example, a lunch break of 30 minutes is subtracted; you can easily change that in the formula if necessary.
Dec 15 2023 01:24 PM
Hi, @Hans Vogelaar 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.
Jan 13 2021 11:53 AM
SolutionIn 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.