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

7 Replies
best response confirmed by Clare76 (Occasional Contributor)
Solution

# Re: Excel - calculating hours worked on single cell values

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.

# Re: Excel - calculating hours worked on single cell values

Thanks Hans for the quick response I will try that formula

# Re: Excel - calculating hours worked on single cell values

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

# Re: Excel - calculating hours worked on single cell values

I don't see an attachment?

# Re: Excel - calculating hours worked on single cell values

Apologies Hans, have attached my example now.

Kind Regards

Clare

# Re: Excel - calculating hours worked on single cell values

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

In AM3:  =SUM(AK3:AL3)

# Re: Excel - calculating hours worked on single cell values

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