A work schedule template in need of one adjustment
Posted by Crowchaser on December 11, 2000 9:33 PM
Let's say that in one cell you have the shift starting and ending times, example 8.25(8:15) - 5.5(5:30) displayed as 8.25-5.5 , is there a value you can put in another cell that would read this cell as being 9.5(9 1/4 hours)? Thanks a bunch for any suggestions.
Posted by Tim Francis-Wright on December 11, 2000 10:10 PM
This is ugly, but will work. For the cell
you described in A1, the following gives 9.25 hours:
=RIGHT(A1,LEN(A1)-FIND("-",A1,1))-LEFT(A1,FIND("-",A1,1)-1)+IF(RIGHT(A1,LEN(A1)-FIND("-",A1,1))<LEFT(A1,FIND("-",A1,1)-1),12,0)
This assumes that you will never have a shift
of over 12 hours. If that's a possibility,
you might want to use 24-hour time. Of course,
this is an easier problem if you have separate
cells for the beginning and ending times.
A useful reference is www.cpearson.com/excel/overtime.htm
NOTE:
It would read better if you defined (while activating Cell B1):
Endtime =RIGHT(A1,LEN(A1)-FIND("-",A1,1))
Begintime = LEFT(A1,FIND("-",A1,1)-1)
Then the formula could simply be
=endtime-begintime+IF(endtime<begintime,12,0)
endtime and begintime will work as long as
you are one cell to the right of the data in question.
Posted by Crowchaser on December 11, 2000 10:17 PM
It really would not be a problem to use two cell for the beginning and ending times lets say they are cells B1 and B2. Thanks either way this would be great. Thanks.
Posted by Michiel Van Doorn on December 12, 2000 3:13 PM
This works if you input numbers in 24 hour clock,
converting minutes to decimals eg 15 mins = .25
=IF(+b1-a1<0,+b1-a1+24,+d1-a1)
where a1 is beginning time
and b1 is end time
