Apr 02 2022 10:47 AM - edited Apr 02 2022 11:22 AM
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) |
Apr 02 2022 11:06 AM
I don't understand. The 1st of January is included in the range from 25th December 2021 to 5th January 2022.
Apr 02 2022 11:13 AM
Apr 02 2022 11:36 AM - edited Apr 02 2022 11:37 AM
SolutionApr 02 2022 11:53 AM
Apr 02 2022 01:18 PM
Apr 02 2022 01:21 PM
=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,"")
Apr 02 2022 02:18 PM
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.
Apr 03 2022 06:01 AM
Apr 03 2022 10:08 AM
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.
Apr 04 2022 09:12 AM
Apr 02 2022 11:36 AM - edited Apr 02 2022 11:37 AM
SolutionIn AJ3:
=SUMPRODUCT((AG3>=AH$3:AH$21)*(AG3<=AI$3:AI$21))>0
Fill down.