SOLVED

Excel - calculating hours worked on single cell values

Copper Contributor

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.

20 Replies
best response confirmed by Clare76 (Copper Contributor)
Solution

@Clare76 

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.

Thanks Hans for the quick response I will try that formula

Hi Hans
I posted earlier this year regarding an issue I had with our staff rota, where I needed to total monthly hours in an excel document based on shifts worked. You provided me with a formula and this has been incredibly helpful in calculating monthly work hours. My issue now is that I want to include holiday hours in the monthly total and can't figure out how to include this in the formula. I have attached an example. Any help gratefully received! (in the attached example a holiday shift is 8 hours)
Kind Regards
Clare



@Clare76 

I don't see an attachment?

@Hans Vogelaar 

 

Apologies Hans, have attached my example now. 

 

Kind Regards

 

Clare

@Clare76 

In AL3:  =COUNTIF(C3:AJ3,"Holiday")*8/24

In AM3:  =SUM(AK3:AL3)

Hans, thanks once again for your excel wizardry, this is perfect. Your knowledge has saved me a lot of time with staff admin, much appreciated! Clare

@Hans Vogelaar 

Hi Sir Hans,

I can't find a way to total the number work hours our employees.

I tried your given formula.

tjforshort_0-1646364939421.png

 

@tjforshort 

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.

Hi @Hans Vogelaar you appear to be somewhat of an excel wizard can you help I tried the formula from above and it didn't appear to work.

https://1drv.ms/x/s!AlkQovMKLEseoksLr_TY2vEd5noq?e=v8L3RS

any chance you could help me with the file and show me how to do this in future

thank you

@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

@V-GEe7  that’s perfect thank you it’s saved me hours so far!!

@Clare76 

 

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

@Tylerb35 

I get "You need access" when I click your link...

@Tylerb35 

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.

@Hans Vogelaar 

 

When you use that calculation how can I a just it for lunch break?

@LloydLL 

In the attached example, a lunch break of 30 minutes is subtracted; you can easily change that in the formula if necessary.

C7D3A08A-99A4-4EBE-9BE1-4C294235F1D1.jpeg

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. 

 

1 best response

Accepted Solutions
best response confirmed by Clare76 (Copper Contributor)
Solution

@Clare76 

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.

View solution in original post