Forum Discussion
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/2023
Andrew
Dave
Rob
12/10/2023
Say you have the data above in a column, is it possible to use a IFS function to follow the logic "IF Dave comes after 10/10/2023 and before 11/10/2023, then true"
But, perhaps there is a more elegant solution using other functions?
Many Thanks
Benson
=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.
=IF(A2=$F$1,AND(INDEX($A$1:A2,LARGE(IF(ISNUMBER($A$1:A2),ROW($A$1:A2)),1))<INDEX(A2:$A$9,SMALL(IF(ISNUMBER(A2:$A$9),ROW(A2:$A$9)-ROW(A1)),1))),"")
You can try this formula in cell B2 and fill down to B9. The name can be entered in cell F1 and the formula dynamically updates the results.
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
=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.
