Help with Nested IF formulas

[arcowens]

Hello. I work as an academic advisor and unfortunately our student database isn't very robust so we have to verify graduation eligibility by hand (yes, I know, my University is archaic). I've been trying to create a formula in excel to automatically calculate for me what year a student is eligible to walk.

Cell B13 is a date field and will identify the students last date of class. I would like for a formula to say:

IF B13 is >= 9/1/2015 AND B13 is <= 8/31/2016 they'd commence in 2016 OR
IF B13 is >= 9/1/2016 AND B13 is <= 8/31/2017 they'd commence in 2017 OR
IF B13 is >= 9/1/2017 AND B13 is <= 8/31/2018 they'd commence in 2018 OR
IF B13 is >= 9/1/2018 AND B13 is <= 8/31/2019 they'd commence in 2019

This is the formula I've been trying and it doesn't seem to work:
=If(And(M13>=9/1/2014,M13<=8/30/2015),"2015",if(And(M13>=9/1/2015,M13<=8/30/2016),"2016”,if(And(M13>=9/1/2016,M13<=8/30/2017),"2017”,if(And(M13>=9/1/2017,M13<=8/30/2018),"2018”
=If(And(M13>=9/1/2014,M13<=8/30/2015),"2015",if(And(M13>=9/1/2015,M13<=8/30/2016),"2016”,if(And(M13>=9/1/2016,M13<=8/30/2017),"2017”,if(And(M13>=9/1/2017,M13<=8/30/2018),"2018”

Does anyone have any suggestions? I've been scouring the internet reading articles on these statements and I can't quite figure out what I'm doing wrong. I'd appreciate any suggestions . Thanks so much!

 

[bbott]

Welcome to the board. Try:

=LOOKUP(M13,{42248,42614,42979,43344,43709},{2016,2017,2018,2019,2020})

 

[ukmikeb]

Hi

Try:-

Excel Workbook
	B	C
13	01/09/2015	2016
14	31/08/2016	2016
15	01/09/2016	2017
16	31/08/2017	2017
17	01/09/2017	2018
18	31/08/2018	2018
19	01/09/2018	2019
20	31/08/2019	2019
Arcowens

Excel 2007

Cell Formulas
Range	Formula
C13	=YEAR(B13)+(MONTH(B13)>8)

hth

 

[bbott]

Hi

Try:-
Arcowens

	B	C
13	01/09/2015	2016
14	31/08/2016	2016
15	01/09/2016	2017
16	31/08/2017	2017
17	01/09/2017	2018
18	31/08/2018	2018
19	01/09/2018	2019
20	31/08/2019	2019

<thead>
</thead><tbody>
</tbody>

Excel 2007

Worksheet Formulas Cell Formula C13 =YEAR(B13)+(MONTH(B13)>8) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

hth

That's a clever solution.

 

[arcowens]

You guys are great - thanks so much!