SOLVED

# check if multiple dates are in multiple date ranges

Occasional Contributor

# check if multiple dates are in multiple date ranges

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: AG AH AI AJ given dates start end formula with output: 1 or 0 1-ian.-2022 2-ian.-2022 6-ian.-2022 =IF((AG3>=AH\$3:AH\$21)*(AG3<=AI\$3:AI\$21);1;0) 2-ian.-2022 5-feb.-2022 15-feb.-2022 =IF((AG4>=AH\$3:AH\$21)*(AG4<=AI\$3:AI\$21);1;0) 3-ian.-2022 23-mar.-2022 7-apr.-2022 =IF((AG5>=AH\$3:AH\$21)*(AG5<=AI\$3:AI\$21);1;0) 4-ian.-2022 25-ian.-2021 4-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

# Re: check if multiple dates are in multiple date ranges

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

# Re: check if multiple dates are in multiple date ranges

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 Alecs2405 (Occasional Contributor)
Solution

# Re: check if multiple dates are in multiple date ranges

In AJ3:

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

Fill down.

# Re: check if multiple dates are in multiple date ranges

@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!

# Re: check if multiple dates are in multiple date ranges

@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

# Re: check if multiple dates are in multiple date ranges

=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,"")

# Re: check if multiple dates are in multiple date ranges

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.

# Re: check if multiple dates are in multiple date ranges

@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

# Re: check if multiple dates are in multiple date ranges

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

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.

# Re: check if multiple dates are in multiple date ranges

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