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