# Thread: Formula to calculate total number of hours in a single cell Thanks: 0 Likes:  1 Post #4933003 (1)

1. ## Formula to calculate total number of hours in a single cell

Hi,

I am currently writing a roster, due to the number of employees its important we keep the spreadsheet as compact as possible, hence the question.

I currently have a formula to calculate number of hours worked in one cell ie 10:15-18:30
Formula as follows;
=IF(TIMEVALUE(LEFT(B3,SEARCH("-",B3)-1))>TIMEVALUE(RIGHT(B3,LEN(B3)-SEARCH("-",B3))),DATEVALUE("24:00:00")-(TIMEVALUE(LEFT(B3,SEARCH("-",B3)-1))-TIMEVALUE(RIGHT(B3,LEN(B3)-SEARCH("-",B3)))), TIMEVALUE(RIGHT(B3,LEN(B3)-SEARCH("-",B3)))-TIMEVALUE(LEFT(B3,SEARCH("-",B3)-1)))

Firstly, I can format the cell to display number of hours worked as 8:15 however I need it to read 8.25 hours, how can I format this within the same cell?

Secondly, how can I adapt this formula to calculate total number of hours worked in 1 week, taking into account days off (as I receive an error)?

Ie
10:15-14:30 off off 10:30-18:30 9:30-16:30 9:00-4:00

Thanks in advance for any assistance you can provide.

Ps. Its important the formula can detect 15 minute increments  Reply With Quote

2. ## Re: Formula to calculate total number of hours in a single cell

Welcome to Mr Excel forum

Maybe this...

 A B C D E F G H 1 Day 1 Day 2 Day 3 Day 4 Day 5 Day 6 Day 7 Total Hours 2 10:15-18:30 10:15-14:30 off off 10:30-18:30 9:30-16:30 9:00-4:00 46,5

Array formula in H2
=SUM(IF(IFERROR(TIMEVALUE(LEFT(A2:G2,SEARCH("-",A2:G2)-1)),0)>IFERROR(TIMEVALUE(RIGHT(A2:G2,LEN(A2:G2)-SEARCH("-",A2:G2))),0),IFERROR(TIMEVALUE(RIGHT(A2:G2,LEN(A2:G2)-SEARCH("-",A2:G2))),0)+1,IFERROR(TIMEVALUE(RIGHT(A2:G2,LEN(A2:G2)-SEARCH("-",A2:G2))),0))-IFERROR(TIMEVALUE(LEFT(A2:G2,SEARCH("-",A2:G2)-1)),0))*24

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.  Reply With Quote

3. ## Re: Formula to calculate total number of hours in a single cell

Thank you!
Will trial the formula and let you know!

Fran  Reply With Quote

4. ## Re: Formula to calculate total number of hours in a single cell Originally Posted by Fb1989 Thank you!
Will trial the formula and let you know!

Fran
You are welcome. Hope it works.

BTW, i think you should consider a different data setup because the formula would be much simpler. Something like

 A B C D E 1 Day Start End Result 2 1 10:15 18:30 46,5 3 2 10:15 14:30 4 3 off 5 4 off 6 5 10:30 18:30 7 6 09:30 16:30 8 7 09:00 04:00

Array formula E2
=SUM(IF(ISNUMBER(B2:B8),IF(B2:B8>C2:C8,1+C2:C8,C2:C8)-B2:B8))*24
Ctrl+Shift+Enter

M.  Reply With Quote

5. ## Re: Formula to calculate total number of hours in a single cell

This formula should work with your original setup

=SUM(IFERROR(MOD(MID(A2:G2,FIND("-",A2:G2)+1,9)-LEFT(A2:G2,FIND("-",A2:G2)-1),1),0))*24

confirm with CTRL+SHIFT+ENTER  Reply With Quote

6. ## Re: Formula to calculate total number of hours in a single cell Originally Posted by barry houdini This formula should work with your original setup

=SUM(IFERROR(MOD(MID(A2:G2,FIND("-",A2:G2)+1,9)-LEFT(A2:G2,FIND("-",A2:G2)-1),1),0))*24

confirm with CTRL+SHIFT+ENTER
Very nice! M.  Reply With Quote

## User Tag List

#### Tags for this Thread

calculate, cell, formula, hours, number #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•