Struggling with conditions of IF statements

[heathermchle]

Hi, I'm working on an interactive spread sheet to use with the teachers that I support and I'm trying to find a way to make the cells in one column display certain text based on the conditions in another. I've literally spent the equivalent of two work days, it feels like, playing with formulas, trying different things. I KNOW there is a way but I just can't figure it out! Spreadsheet is below:

The user will input data from B8 and G8 ( I will likely move G next to B so the rest of the table populates from there.

In Column H, starting at row 8, I need an IF statement that plays on the conditions in row G8. So...

If G8 is in between I1 and G3, the text says Fall.
If G8 is greater than G3 but less than G4, it says Winter.
If G8 is greater than G4, but less than G5, it says Spring.

I1, and G3:G5 are fixed cells.

Column K works and calculates correctly based on this text (from a method I had previously tried, I decided to leave it there since I hadn't broken it yet!)

Once the text shows up in Column H correctly, I will conditionally format it to 3 different colors.

I just can't figure out how to get the date conditions to show properly. I've tried =IF( and also =IF(AND... all kinds of ways.

Someone please help!

 

[johnny51981]

You can do this with an easy and slight modification.

Step 1: Separate your Assessment Period Timelines into 2 Columns. One for the Start and One for the End. So it would go "Assessment Season", "Assessment Start", "Assessment End"...but feel free to call these something different.
Step 2: In Cell H1, use the formula of =LOOKUP($G8,[Assessment Start Dates],[Assessment Seasons])
Step 3: Double check the years you using for your Winter and Spring Assessments.

 

[heathermchle]

You can do this with an easy and slight modification.

Step 1: Separate your Assessment Period Timelines into 2 Columns. One for the Start and One for the End. So it would go "Assessment Season", "Assessment Start", "Assessment End"...but feel free to call these something different.
Step 2: In Cell H1, use the formula of =LOOKUP($G8,[Assessment Start Dates],[Assessment Seasons])
Step 3: Double check the years you using for your Winter and Spring Assessments.

Thank you, I'm going to try this now and see how it turns out. Silly mistakes on the dates, I've found that it's been small things like that that make some attempts not work.

Anyone have any tips on why a date that is BEFORE another date turns up as FALSE when I say IF is <=? I've tried formatting all the dates to the same thing like "Short Date" or "Text" with no luck.

 

[heathermchle]

You can do this with an easy and slight modification.

Step 1: Separate your Assessment Period Timelines into 2 Columns. One for the Start and One for the End. So it would go "Assessment Season", "Assessment Start", "Assessment End"...but feel free to call these something different.
Step 2: In Cell H1, use the formula of =LOOKUP($G8,[Assessment Start Dates],[Assessment Seasons])
Step 3: Double check the years you using for your Winter and Spring Assessments.

I've tried it every which way, for a minute it showed the word "Period" in H9 but then was NA in all of the other cells going down. I feel like I should give up on this but.... it seems like... isn't this what excel does??? I'm so frustrated.

 

[heathermchle]

Wow! I've got it to work for some of the rows but after H11, it stops calculating correctly. I've noticed that this is where the formula keeps going past B6, which doesn't have a forumla. How do I keep B4:B6 static? Which formula and where should I insert it in the formula?

 

[heathermchle]

Here is the formula that is in H11: =LOOKUP(2,1/(($E12>=$C$4)*($E12<=$C$6)),B7)

 

[KRice]

Let me know if this does what you want:

MrExcel_20220607.xlsx
	A	B	C	D	E	F	G	H	I
1								Program Year Start Date:	8/1/2022
2			Start	End
3		Fall	8/1/2022	9/30/2022		Enrollment Cut-Off:	10/31/2022
4		Winter	10/1/2022	1/27/2022		Enrollment Cut-Off:	12/30/2022
5		Spring	1/28/2022	4/28/2023		Enrollment Cut-Off:	2/28/2023
6
7		Name		DOB	Date of 3rd Bday	Child rated using	FDA	1st Assessment
8		name1		4/8/2019	4/8/2022	Preschool Comprehensive View	8/1/2022	Fall
9		name2		10/21/2018	10/21/2021	Preschool Comprehensive View	8/1/2022	Fall
10		name3		5/3/2019	5/3/2022	Preschool Comprehensive View	11/3/2022	Winter
11		name4		3/20/2020	3/20/2023	Infant Toddler Comprehensive View	1/12/2023	Spring
12		name5		12/7/2018	12/7/2021	Preschool Comprehensive View	4/3/2023	too late
Sheet5

Cell Formulas
Range	Formula
I1	I1	=C3
E8:E12	E8	=EDATE(D8,36)
H8:H12	H8	=IF(G$8<$I$1,"too early",IF(LARGE(($G8>$G$3:$G$5)*{1;2;3},1)+1>3,"too late",INDEX($B$3:$B$5,LARGE(($G8>$G$3:$G$5)*{1;2;3},1)+1)))

 

[heathermchle]

Let me know if this does what you want:

MrExcel_20220607.xlsx
	A	B	C	D	E	F	G	H	I
1								Program Year Start Date:	8/1/2022
2			Start	End
3		Fall	8/1/2022	9/30/2022		Enrollment Cut-Off:	10/31/2022
4		Winter	10/1/2022	1/27/2022		Enrollment Cut-Off:	12/30/2022
5		Spring	1/28/2022	4/28/2023		Enrollment Cut-Off:	2/28/2023
6
7		Name		DOB	Date of 3rd Bday	Child rated using	FDA	1st Assessment
8		name1		4/8/2019	4/8/2022	Preschool Comprehensive View	8/1/2022	Fall
9		name2		10/21/2018	10/21/2021	Preschool Comprehensive View	8/1/2022	Fall
10		name3		5/3/2019	5/3/2022	Preschool Comprehensive View	11/3/2022	Winter
11		name4		3/20/2020	3/20/2023	Infant Toddler Comprehensive View	1/12/2023	Spring
12		name5		12/7/2018	12/7/2021	Preschool Comprehensive View	4/3/2023	too late
Sheet5

Cell Formulas
Range	Formula
I1	I1	=C3
E8:E12	E8	=EDATE(D8,36)
H8:H12	H8	=IF(G$8<$I$1,"too early",IF(LARGE(($G8>$G$3:$G$5)*{1;2;3},1)+1>3,"too late",INDEX($B$3:$B$5,LARGE(($G8>$G$3:$G$5)*{1;2;3},1)+1)))

It works only if I remove the dates from column G. Otherwise it says, "#VALUE, An array value could not be found."

 

[KRice]

You've rearranged your sheet so the references need to be adjusted:

MrExcel_20220607.xlsx
	A	B	C	D	E	F	G	H	I	J
1									Program Year Start Date:	8/1/2022
2			Start	End	Cut Off
3		Fall	8/1/2022	9/30/2022	10/31/2022
4		Winter	10/1/2022	1/27/2022	12/30/2022
5		Spring	1/28/2022	4/28/2023	2/28/2023
6
7		Name		DOB	FDA	Date of 3rd Bday	Child rated using	1st Assessment
8		name1		4/8/2019	8/1/2022	4/8/2022	Preschool Comprehensive View	Fall
9		name2		10/21/2018	8/1/2022	10/21/2021	Preschool Comprehensive View	Fall
10		name3		5/3/2019	11/3/2022	5/3/2022	Preschool Comprehensive View	Winter
11		name4		3/20/2020	1/12/2023	3/20/2023	Infant Toddler Comprehensive View	Spring
12		name5		12/7/2018	4/3/2023	12/7/2021	Preschool Comprehensive View	too late
Sheet5

Cell Formulas
Range	Formula
J1	J1	=C3
F8:F12	F8	=EDATE(D8,36)
H8:H12	H8	=IF(E$8<$J$1,"too early",IF(LARGE(($E8>$E$3:$E$5)*{1;2;3},1)+1>3,"too late",INDEX($B$3:$B$5,LARGE(($E8>$E$3:$E$5)*{1;2;3},1)+1)))

 

[KRice]

I noticed a few typos in my version, but I see that you are now using a different criteria. You originally said to use the Cut-Off dates for determining the Fall/Winter/Spring terms, but your latest formula shows that you are using the Start and End dates. Which do you want? The LOOKUP formula used like that will return the last value array position satisfying the conditional criteria...and it would look like this if you want to use Start & End dates rather than the cutoff dates.

=LOOKUP(2,1/(($E8>=$C$3:$C$5)*($E8<=$D$3:$D$5)),$B$3:$B$5)