Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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.

Check out our Excel Resources

Re: A work schedule template in need of one adjustment

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.


Re: A work schedule template in need of one adjustment

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.

Re: A work schedule template in need of one adjustment

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.