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

tHi,

Use this instead to work out imes across midnight

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

Mike

"MJKelly" wrote:


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

On Sat, 17 May 2008 07:10:01 -0700 (PDT), MJKelly
wrote:


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

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

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

On Sat, 17 May 2008 09:17:59 -0700 (PDT), MJKelly
wrote:


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

I don't understand your questions:

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.

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

Would this make it easier?

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