Problem where end time is less than start time

[MJKelly]

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

 

[Mike H]

tHi,

Use this instead to work out imes across midnight

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

Mike

 

[Ron Rosenfeld]

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

 

[MJKelly]

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

 

[Ron Rosenfeld]

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