Time between days

R

RamOst

I'm setting up a shift roster and need to determine whether a given time is
between the start time and end time of a shift where the shift runs across 2
calendar days
eg start 23:00 finish 06:00

would appreciate any help - it's driving me mad
 
A

Ardus Petus

Assuming start time is in $1$1 and end time in $B$1, ant time to test in D1,
enter formula:
=AND(D1+(D1<$A$1)>=A$1,D1+(D1<$A$1)<=$B$1+($B$1<$A$1))
which you can drag down

The formula adds 1 day to times below start time.

HTH
 
A

Arvi Laanemets

Hi

=EndTime-StartTime+(AndTime<StartTime)

Replace EndTime/StartTime with cell references to times in valid time
format. (The wormula works when time difference remains less than 24 hours)
 
A

Ardus Petus

So much simpler!!!


Arvi Laanemets said:
Hi

=EndTime-StartTime+(AndTime<StartTime)

Replace EndTime/StartTime with cell references to times in valid time
format. (The wormula works when time difference remains less than 24
hours)
Click to expand...
 
A

Ardus Petus

Arvi,

I loved your solution, but it does not answer the OP's question.
It only gives an interval duration

Cheers,
 
A

Arvi Laanemets

Hi

With StartTime in A2 and EndTime in B2:
=OR(AND(C2>=A2,C2<=B2,B2>A2),AND(C2>=A2,A2>B2),AND(C2<B2,A2>B2))
 
A

Arvi Laanemets

Hi

Some modifications:

=OR(AND(C2>=A2,C2<=B2,B2>A2),AND(OR(C2>=A2,C2<=B2),A2>B2))
=(1=((C2>=A2)*(C2<=B2)*(B2>A2)+((C2>=A2)+(C2<=B2))*(A2>B2)))
=AND(((C2>=A2)*(C2<=B2)*(B2>A2)+((C2>=A2)+(C2<=B2))*(A2>B2)))
 
A

Ardus Petus

This time, my formula is shorter and clearer:
=AND(D1+(D1<$A$1)>=A$1,D1+(D1<$A$1)<=$B$1+($B$1<$A$1))
 
Top