Forum Discussion
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
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.
- PeterBartholomew1
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 greatThe 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.
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.
