SOLVED

check if multiple dates are in multiple date ranges

Brass Contributor

Hello guys,

can you please help me with an ideea about this problem that I'm facing for a project please?

I need an formula that checks if these given dates are included in the date ranges (from the right) and return an value if true.

For example: 1 jan is not included here, but 2-3-4-5-6 jan is included. I could not find any solution to this. Logical functions do not work if I need to drag downwards the formula)

(I have just edited the post with better example bellow)

column: AGAHAIAJ
given datesstartendformula with output: 1 or 0
1-ian.-20222-ian.-20226-ian.-2022=IF((AG3>=AH$3:AH$21)*(AG3<=AI$3:AI$21);1;0)
2-ian.-20225-feb.-202215-feb.-2022=IF((AG4>=AH$3:AH$21)*(AG4<=AI$3:AI$21);1;0)
3-ian.-202223-mar.-20227-apr.-2022=IF((AG5>=AH$3:AH$21)*(AG5<=AI$3:AI$21);1;0)
4-ian.-202225-ian.-20214-feb.-2022=IF((AG6>=AH$3:AH$21)*(AG6<=AI$3:AI$21);1;0)
5-ian.-2022  =IF((AG7>=AH$3:AH$21)*(AG7<=AI$3:AI$21);1;0)
6-ian.-2022  =IF((AG8>=AH$3:AH$21)*(AG8<=AI$3:AI$21);1;0)
7-ian.-2022  =IF((AG9>=AH$3:AH$21)*(AG9<=AI$3:AI$21);1;0)
8-ian.-2022  =IF((AG10>=AH$3:AH$21)*(AG10<=AI$3:AI$21);1;0)
10 Replies

@Alecs 

I don't understand. The 1st of January is included in the range from 25th December 2021 to 5th January 2022.

Hello Hans.
thank you for your message. It is right. I've forgot to change the range 25 dec to 5 jan in order to match my example. I've edited my initial post right now and displays the right content. Sorry for that.
The problem here is that a logical function can't be dragged downwards in order to change the references from another cells automatically. It will work only if I edit row by row manually.
Please let me know if you have any questions in order to clarify them and find an solution.

best response confirmed by Alecs (Brass Contributor)
Solution

@Alecs 

In AJ3:

=SUMPRODUCT((AG3>=AH$3:AH$21)*(AG3<=AI$3:AI$21))>0

Fill down.

@Hans Vogelaar
many thanks. You saved my deadline :) much appreciated
I will leave this post open for a few more hours in case something else happens.
Wish you all the best!
@Hans Vogelaar
one question please
how can I use the formula that you've provided: =SUMPRODUCT((AG3>=AH$3:AH$21)*(AG3<=AI$3:AI$21))>0
with an IF statement?
example: IF value of of an cell A1=TRUE than do the SUMPRODUCT.
looks like it's not working

@Alecs 

=IF(A1,SUMPRODUCT((AG3>=AH$3:AH$21)*(AG3<=AI$3:AI$21))>0)

or perhaps

IF(A1,SUMPRODUCT((AG3>=AH$3:AH$21)*(AG3<=AI$3:AI$21))>0,"")

 

@Alecs 

Using 365 one can avoid the need to copy down the formula by referencing entire arrays

= IF(criterion, 
    (givenDate>=startDate)*(givenDate<=endDate),
    "")

It looks a bit different, but it would, coming from someone who describes the A1 direct referencing notation as 'an abomination' that has no place in any programming environment.

@Peter Bartholomew
Thank you for this suggestion. I've never heared about it and based on my recent searches on google about 365 coding or programming is not what you've described above. Can you please share a name or an link? It looks great

@Alecs 

The story of modern Excel started with Joe McDaid's announcement in Sept 2018

Preview of Dynamic Arrays in Excel - Microsoft Tech Community.

Later posts include Lambda functions that allow the user to pass parameters into a formula and then some helper functions that pass sequences of parameters into a Lambda function.

 

The solutions one can develop using the new functionality work at the array level with little requirement for concepts such as relative referencing.

 

BTW.  Without sample results, I may well have misinterpreted the calculation you wish to perform the calculation you wish to perform.  More complex situations can involve the '@' operator or even the MAP helper function.

Amazing. I am very grateful for all the information that you've provided. I will check them and work with them from now on.
Best regards,
Alecs
1 best response

Accepted Solutions
best response confirmed by Alecs (Brass Contributor)
Solution

@Alecs 

In AJ3:

=SUMPRODUCT((AG3>=AH$3:AH$21)*(AG3<=AI$3:AI$21))>0

Fill down.

View solution in original post