SOLVED

IFS Function - producing a true if between two values

Copper Contributor

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

 

 

6 Replies

@benson88 

=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.

if between two dates.png

 

@OliverScheurich 

 

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

Edit:
Hopefully this is a clearer explanation of my query.

Apologies for not being able to post the excel sheet I am working on itself, as the contents contain personal data.
best response confirmed by Hans Vogelaar (MVP)
Solution

@benson88 

=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.

check if value between two dates.png

This is very useful, thank you Oliver! Hopefully I can implement this into my work.
Much Appreciated.
Benson

@benson88 

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.

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@benson88 

=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.

check if value between two dates.png

View solution in original post