Problem where end time is less than start time

Problem where end time is less than start time

Post by MJKell » Sun, 18 May 2008 23:10:01



Hi,

I use start and end time data to fill staff resource. I want to build
in some error capture where the end of the duty is less than the value
of the start of the duty. However, on occasion we have staff starting
at 22:00 and finishing at 06:00. This would result in an error
although the duty is legitimate. How can I get around this issue?

kind regards,
Matt
 
 
 

Problem where end time is less than start time

Post by TWlrZSB » Sun, 18 May 2008 23:30:00

tHi,

Use this instead to work out imes across midnight

=IF(A1>B1,B1+1-A1,B1-A1)

Mike

 
 
 

Problem where end time is less than start time

Post by Ron Rosenf » Mon, 19 May 2008 00:11:24

On Sat, 17 May 2008 07:10:01 -0700 (PDT), MJKelly < XXXX@XXXXX.COM >



I think the only fool-proof way to get around that issue would be to include
the date along with the times.

You could, possibly, flag an error if the time interval was greater than some
pre-set value. For example, if you would NEVER have shifts of > 12 hrs, you
could check for that. This would be less fool-proof.

By the way, a simple way of handling shifts that might span midnight, would be

=MOD(EndTime-StartTime,1)

But if you are going to include the dates, then it could be just simply:
EndDateTime-StartDateTime.

--ron
 
 
 

Problem where end time is less than start time

Post by MJKell » Mon, 19 May 2008 01:17:59


Ron,
Thanks for the assistance. Interesting thoughts.

One thing which may help is the fact that the working day is 06:00 to
06:00. So if 06:00 was the finish time (or anything from 00:00 to
06:00) then the end time must be the following date. Would this make
it easier? What would I do? I don't want to ask for the input date,
but the range where the input takes place is attached to a working
day, so maybe the date could be applied automatically. The start of
week date would be available for reference.

Thanks,
Matt
 
 
 

Problem where end time is less than start time

Post by Ron Rosenf » Mon, 19 May 2008 01:44:48

On Sat, 17 May 2008 09:17:59 -0700 (PDT), MJKelly < XXXX@XXXXX.COM >



I don't understand your questions:


What is the difference between "finish time" and "end time"?


Make what easier?


If you are writing about computing elapsed time if the shift spans midnight,
them =MOD(EndTime-StartTime,1) is about as easy as it gets.

If you are writing about deciding whether you entered a start time that was
after the end time, and the only information you have is that a shift may not
span 6AM, then maybe:

=IF(MOD("6:00"-StartTime,1)<MOD(EndTime-StartTime,1),"error",MOD(EndTime-StartTime,1))

--ron