Forum Discussion
benson88
Oct 12, 2023Copper Contributor
IFS Function - producing a true if between two values
Hi, I am trying to create a spread sheet where the raw data i am given is contained in one column. I am trying to find the solution using an IFS function. 10/10/2023 Dave Steph Neil 11/10...
- Oct 12, 2023
=IF(A2=$F$1,AND(INDEX($A$1:A2,LARGE(IF(ISNUMBER($A$1:A2),
ROW($A$1:A2)),1))=$G$1,INDEX(A2:$A$9,SMALL(IF(
ISNUMBER(A2:$A$9),ROW(A2:$A$9)-ROW(A1)),1))=$G$2),"")Thank you. In my first reply i didn't correctly understand what you want to do. With this formula the dates can be specified in cells G1 and G2. If someone doesn't work with Office 365 or Excel for the web or Excel 2021 the formula has to be entered with ctrl+shift+enter as an arrayformula. WAHR is TRUE and FALSCH is FALSE in german Excel.
benson88
Oct 12, 2023Copper Contributor
Thank you Oliver for taking the time to look at my query.
I was wondering if there is a way to do this if where it comes back as true only if between the specified dates. for example, in the solution you gave the Dave's come back as true despite one of them not being between 10/10/2023 and 11/10/2023.
For context, I have been given a list of people that enter the building, which is in much the same format as in the example: the date and below that the names of the people that entered on that date. this contain data for multiple dates all in one column. What I'm looking to do is get a yes or no to the question "did Dave enter the building between 10/10/2023 and 11/10/2023"
Hopefully this is a clearly explanation of my query.
apologies for being able to post the excel sheet I am working on itself, as the contents contain personal data.
Many Thanks
Benson
OliverScheurich
Oct 12, 2023Gold Contributor
=IF(A2=$F$1,AND(INDEX($A$1:A2,LARGE(IF(ISNUMBER($A$1:A2),
ROW($A$1:A2)),1))=$G$1,INDEX(A2:$A$9,SMALL(IF(
ISNUMBER(A2:$A$9),ROW(A2:$A$9)-ROW(A1)),1))=$G$2),"")
Thank you. In my first reply i didn't correctly understand what you want to do. With this formula the dates can be specified in cells G1 and G2. If someone doesn't work with Office 365 or Excel for the web or Excel 2021 the formula has to be entered with ctrl+shift+enter as an arrayformula. WAHR is TRUE and FALSCH is FALSE in german Excel.
- benson88Oct 12, 2023Copper ContributorThis is very useful, thank you Oliver! Hopefully I can implement this into my work.
Much Appreciated.
Benson- OliverScheurichOct 12, 2023Gold Contributor
You are welcome. In the attached file the result is shown in cell E1. This is better if there are e.g. 10000 entries in column A and the TRUE result is in cell B8000.