UKC

I have an arrival time "A" (1-24 equating to whole hours of 24 hour clock)
and a departure time "D" in the same form

I have a test time "T"

If T lies between the arrival and depature time (between includes equal to either for this purpose), I want a "Y"; if not a "N"

Trivial where both A and D are the same day, but I'm struggling to get the ones that start, say, 23 and end, say, 2 into a simple formula. Is there a prettier way to do it than lots of nested IF functions?

In reply to JJL:

If your arrival time is apparently smaller than your departure time, e.g. D=23:00 and A=01:00 then add a day to the arrival time.

In reply to hokkyokusei:

This is probably the neatest way, it's still an IF loop but you can just break it out into a separate column i.e.

D_adj=IF(A<=D,D,D+24)

Then replace D with D_adj in your trivial calculation.

Saves you nesting the IFs in your existing formula

In reply to JJL:

Assume A in cell A1 and B in cell A2 how about the following formula in C1 to give the result?

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

In reply to JJL:
I would convert arrival times to hours greater than 24 in an adjacent cell using if statements(format [h]:mm:ss) then use =if(c3=median(c1,c2,c3),"yes","no") where c1 is arrival time, c2 departure and c3 test (converted times)

Edit: ignore the formula bit, just read you are using whole numbers

In reply to JJL:


In the above example, if T = 1, how do you know which day it occurs on? If it's 01:00 on the first day then the answer is 'N' but if it's 01:00 on the second day, the answer is 'Y'. Or is this irrelevant in the context of what you need?

In reply to AndyC:

T=1 is always first day

In reply to JJL:


OK, then it seems to me that, since it looks like there can never be more than 24 hours between arrival and departure and T is always on the first day, then when D < A you only need to test for T >= A, otherwise test for (T >= A) AND (T <= D).

This pseudoformula might do it...

=if(OR(AND(A<D, T>=A, T<=D),AND(A>D, T>=A)),"Y","N")

In reply to AndyC:

Ooooh. I LIKE that.

I shall *run* into the office tomorrow to try it!

Thanks to all contributors to the thread. You may have helped in a small step towards shorter waits in A&E(*).








(*) Only because I am arguing that we look too hard at "arrivals" profile, and not hard enough at concurrency (i.e. how full A&E is).

In reply to AndyC:

I've thrown you a bum steer. T=1 can be second day, so your formula does miss where A is first day but D is second and T lies between them.

Sorry for the misinformation; I'd forgotten my own nomencalture

In reply to JJL:


You like nested AND and ORs but don't like nested IFs? Are you some kind of function racist???

I assume one of the solutions worked out for you?

In reply to JJL:


No problem for me but I don't see how it can work if you have no way of differentiating between, for example, 01:00 on day 1 and 01:00 on day 2. You must have some additional date information in the Excel sheet?

In reply to AndyC:
>"you have no way of differentiating between, for example, 01:00 on day 1 and 01:00 on day 2"

=IF(((A + E) = NHS), "Day 2", "Day 1"):¬)

In reply to AndyC:

Yes, you're right. All the solutions given here assume that you are dealing with a 24 hour period which just happens to run across midnight, creating the problem. If you have more than 24 hours then you start to find that there is not enough information to give a Y/N answer in all cases. Once you have 48 hours then there are no combinations which can be solved.

If there's a date column in the spreadsheet, then you should just combine the date and time into a date-time (in fact all dates in excel are date-times) and you can use simple logic again. Otherwise, you need to improve the data collection.

In reply to JLS:


I see! A nasty case of mañana syndrome!

In reply to AndyC:

A is always in Day 1
D is sometimes in day 2
the test time is integers 1 through 24 atop columns
I want to mark each column that lies "between" A and D
Where D sometimes has tripped past midnight, I still want to mark the columns 1...D (but will be counted as the same 24 hour period - this doesn't matter because I'm summing trends)

In reply to JJL:

OK - if I have understood you correctly, then this should do it:

=if(OR(AND(A<=D, T>=A, T<=D), AND(A>=D, T>=A), AND(A>=D, T<=D)), "Y", "N")