Validating times that span midnight

[Galley]

I am working on a spreadsheet to track machine downtime. The user enters the start time and end time of any machine downtime. The time entered must occur on or after the lot start time or shift start time (whichever occurs first) and on or before the lot end time or shift end time, (whichever occurs first):

1st shift starts at 07:00
2nd shift starts at 15:00
3rd shift starts at 23:00</SPAN>

A1 = lot start</SPAN>
A2 = lot end</SPAN>
B1 = downtime start</SPAN>
C1 = downtime end</SPAN>

Validating the downtime (Time between A1 and A2) works fine as long as that time does not span midnight. </SPAN>
Likewise,

=IF(B1>=A1, B1<=A2)

validates as a TRUE statement as long as the time does not span midnight.</SPAN>
I don’t have any issue with calculating the length of the downtime; I just need to write a custom validation formula that will only accept values within the range. Is there any way to do it without involving dates? </SPAN>
Thanks</SPAN>

 

[Galley]

It seems Excel can validate date or time, but not both. I'm running out of ideas.

 

[barry houdini]

Try this version

=(B1>=A1)+(B1<=A2)+(A2< A1)=2<a1)=2< pre=""></a1)=2<>

 

[Galley]

That works. Thank you very much!

I was aready using the following formula to calculate the length of the downtime. It will work with times that span midnight, so I knew the solution had to be similar.

(B1>C1)+(C1-B1)