Formula to calculate total number of hours in a single cell

Fb1989

New Member
Joined
Oct 19, 2017
Messages
2
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Upvote 0
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​

<tbody>
</tbody>


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

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top