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

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
--
AP

"RamOst" a écrit dans le message de news:
...
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

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)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"RamOst" wrote in message
...
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

So much simpler!!!


"Arvi Laanemets" a écrit dans le message de news:
...
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)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"RamOst" wrote in message
...
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

Arvi,

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

Cheers,
--
AP

"Arvi Laanemets" a écrit dans le message de news:
...
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)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"RamOst" wrote in message
...
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

Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Ardus Petus" wrote in message
...
Arvi,

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

Cheers,
--
AP

"Arvi Laanemets" a écrit dans le message de news:
...
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)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"RamOst" wrote in message
...
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

Hi

Some modifications:

=OR(AND(C2=A2,C2<=B2,B2A2),AND(OR(C2=A2,C2<=B2) ,A2B2))
=(1=((C2=A2)*(C2<=B2)*(B2A2)+((C2=A2)+(C2<=B2)) *(A2B2)))
=AND(((C2=A2)*(C2<=B2)*(B2A2)+((C2=A2)+(C2<=B2) )*(A2B2)))

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Arvi Laanemets" wrote in message
...
Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Ardus Petus" wrote in message
...
Arvi,

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

Cheers,
--
AP

"Arvi Laanemets" a écrit dans le message de news:
...
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)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"RamOst" wrote in message
...
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

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))

--
AP

"Arvi Laanemets" a écrit dans le message de news:
...
Hi

Some modifications:

=OR(AND(C2=A2,C2<=B2,B2A2),AND(OR(C2=A2,C2<=B2) ,A2B2))
=(1=((C2=A2)*(C2<=B2)*(B2A2)+((C2=A2)+(C2<=B2)) *(A2B2)))
=AND(((C2=A2)*(C2<=B2)*(B2A2)+((C2=A2)+(C2<=B2) )*(A2B2)))

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Arvi Laanemets" wrote in message
...
Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Ardus Petus" wrote in message
...
Arvi,

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

Cheers,
--
AP

"Arvi Laanemets" a écrit dans le message de news:
...
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)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"RamOst" wrote in message
...
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