IsBetween...

[Deserrol]

Hi,
I've not seen that Microsoft have chosen to include this kind of function (am happy to be wrong) in any release???

What I'm trying to do is very simple but believe that either it is only achievable through an array function or using VB. VB works no problem but I don't want to go that route and am really struggling to get an array function going.

What I'm trying to do is conceptually very simple:

I have a date, REF DATE, and I want to test whether it exists within a table of FROM and TO dates.

I'm looking for a function to return a 1/0 or true/false if REFDATE is between any of the individual FROM and To dates in the table.

I'm almost bald and pulling my hair out on this does not help so maybe you can help me out please...

Basically:

REF DATE [x]

Variable length date table:
From (1,1) To (1,2)
From (2,1) To (2,2)
From (3,1) To (3,2)
From (4,1) To (4,2)
From (5,1) To (5,2)
From (etc999,1) To (etc999,2)

How many times does [x] appear between (1,1)-->(1,2), AND between (2,1)->(2,2) AND (3,1)-->(3,2), etc... As I say, I can do it using VBA and also using a custom function but want a formula that does it,and I reckon it's an array formula which I cannot sort

Basically it is for a timsheeting system which will provide workers with a timesheet containing only valid working days, i.e. ignoring all of the date sin the table above such as Christmas, Easter and their annual holidays which they will input to the table.

 

[Tetra201]

Maybe

=COUNTIFS(A:A,"<="&D1,B:B,">="&D1)

 

[estevaoba]

Try this:

=IF(AND(REF_DATE>=DATE_FROM,REF_DATE<=DATE_TO),TRUE,FALSE)

 

[Deserrol]

Tetra201 - FFlipSake: Hero. Jeez, I'd tried something like that before before. Many thanks!!!!!

And just to clarify, using COUNTIFS will evaluate each of the criteria-range elements alongside each other?

 

[Deserrol]

Try this:

=IF(AND(REF_DATE>=DATE_FROM,REF_DATE<=DATE_TO),TRUE,FALSE)

Thanks estevaoba, but have tried that is is full on anomalies. Tetra201's thing is as simple and appears to work.

Thanks to both of you

 

[Deserrol]

Hi estevaoba, thanks but the limitations are around the data being used, bot your formula which works hit-and-miss. However I will try that as an array formula but Tetra201's response does not require an array formula.
Many thanks
D

 

[estevaoba]

I failed to finish my post above.

I was going to suggest using that formula and copy down for a helper column.

=IF(AND(REF_DATE>=DATE_FROM,REF_DATE<=DATE_TO),TRUE,FALSE)

Then you'd just have to count TRUES.

 

[FDibbins]

I failed to finish my post above.

I was going to suggest using that formula and copy down for a helper column.

=IF(AND(REF_DATE>=DATE_FROM,REF_DATE<=DATE_TO),TRUE,FALSE)

Then you'd just have to count TRUES.

If you wanted to take that approach - and IF it worked, you could skip the IF statement, and just use the AND()...
=AND(REF_DATE>=DATE_FROM,REF_DATE<=DATE_TO)
This will return either a TRUE or FALSE

PLUS if you added -- at the start...
=--AND(REF_DATE>=DATE_FROM,REF_DATE<=DATE_TO)
this will return either 1 (T) or 0 (F) and you could then just use a simple =SUM()