SOLVED

Excel - calculating hours worked on single cell values

%3CLINGO-SUB%20id%3D%22lingo-sub-2057979%22%20slang%3D%22en-US%22%3EExcel%20-%20calculating%20hours%20worked%20on%20single%20cell%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2057979%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20figure%20out%20what%20I'm%20sure%20is%20a%20simple%20excel%20query.%20I%20need%20to%20calculate%20total%20weekly%20hours%20on%20a%20staff%20rota%20and%20the%20rota%20is%20formatted%20with%20each%20shift%20eg%2009%3A00-17%3A00%20in%20one%20single%20cell.%20Is%20there%20a%20way%20to%20sum%20these%20hours%3F%20Previously%20it%20was%20down%20manually%20but%20means%20there%20is%20room%20for%20error.%20I%20have%20seen%20other%20examples%20splitting%20the%20shift%20into%20two%20cells%20(start%20and%20end%20time)%20but%20my%20manager%20wants%20to%20keep%20the%20format%20as%20is.%20I've%20attached%20an%20example%20and%20would%20appreciate%20any%20feedback.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2057979%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2058184%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20calculating%20hours%20worked%20on%20single%20cell%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2058184%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928060%22%20target%3D%22_blank%22%3E%40Clare76%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20I3%20as%20an%20%3CSTRONG%3Earray%20formula%3C%2FSTRONG%3E%2C%20confirmed%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(IFERROR(MOD(TIMEVALUE(RIGHT(B3%3AH3%2C5))-TIMEVALUE(LEFT(B3%3AH3%2C5))%2C1)%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EApply%20the%20custom%20number%20format%20%5Bh%5D%3Amm%20to%20I3%2C%20then%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2058816%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20calculating%20hours%20worked%20on%20single%20cell%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2058816%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Hans%20for%20the%20quick%20response%20I%20will%20try%20that%20formula%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2233556%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20calculating%20hours%20worked%20on%20single%20cell%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2233556%22%20slang%3D%22en-US%22%3EHi%20Hans%3CBR%20%2F%3EI%20posted%20earlier%20this%20year%20regarding%20an%20issue%20I%20had%20with%20our%20staff%20rota%2C%20where%20I%20needed%20to%20total%20monthly%20hours%20in%20an%20excel%20document%20based%20on%20shifts%20worked.%20You%20provided%20me%20with%20a%20formula%20and%20this%20has%20been%20incredibly%20helpful%20in%20calculating%20monthly%20work%20hours.%20My%20issue%20now%20is%20that%20I%20want%20to%20include%20holiday%20hours%20in%20the%20monthly%20total%20and%20can't%20figure%20out%20how%20to%20include%20this%20in%20the%20formula.%20I%20have%20attached%20an%20example.%20Any%20help%20gratefully%20received!%20(in%20the%20attached%20example%20a%20holiday%20shift%20is%208%20hours)%3CBR%20%2F%3EKind%20Regards%3CBR%20%2F%3EClare%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional 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.

7 Replies
best response confirmed by Clare76 (Occasional 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