|
Time between days
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 |
Time between days
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 |
Time between days
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 |
Time between days
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 |
Time between days
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 |
Time between days
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 |
Time between days
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 |
| All times are GMT +1. The time now is 02:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com