Hello,

I want to return a fixed value if the start date falls within two dates. So the start date would be 01/09/2001 the end date would be 31/08/2002 and the value would be 01/02 representing the academic year. The if the date entered fell between these two dates the value returned would represent that academic year. Also I would like to have a formula containing multiple values for each academic year.

So far I have this table:

A B C
01/09/2000 31/08/2001 00/01
01/09/2001 31/08/2002 01/02
01/09/2002 31/08/2003 02/03
01/09/2003 31/08/2004 03/04
01/09/2004 31/08/2005 04/05
01/09/2005 31/08/2006 05/06
01/09/2006 31/08/2007 06/07
01/09/2007 31/08/2008 07/08
01/09/2008 31/08/2009 08/09
01/09/2009 31/08/2010 09/10
01/09/2010 31/08/2011 10/11

And this formula:

=IF(AND(R6 >= B1,R6 <= B2, R6, “”)

will return R6 in case it is in between B1 and B2 inclusive.

But it doesn’t seem to work.

Help please, thanks

Have you got the cells formatted properly as dates, rather than text?
IIRC for that format, you need to specify is a custom format of dd/mm/yyyy. Do it for all the cells with dates in.
Also, academic year column will probably be formatted as text. I’d just use a number for the start year e.g. 01/02 = 1 because I’m not sure the formula presented will return text.
HTH

I’m after having both the start date and end date in separate columns with the academic year in a separate column.

What about this?

=IF(AND(F2 >= B2,F2 <= C2, D2, “0”)

B2 C2 D2 E2 F2
01/09/2000 31/08/2001 00/01 12/12/2000

=IF(AND(F2 >= B2,F2 <= C2, D2, “0”)

where the hell is Stoner when you need him….?

Have a look here – Nested If statement, I think the and is in the wrong place.

http://www.techonthenet.com/excel/formulas/if_nested.php

Do you not just need to close the AND argument with a bracket.

=IF(AND(R6 >= B1,R6 <= B2), R6, “”)

That’s good if it’s a fixed value. The data entered will be a date which will fall between a start and end date for an academic year.

I have this which will calculate what it would be today:

=IF(MONTH(TODAY())<9,YEAR(TODAY())-1&”/”&RIGHT(YEAR(TODAY()),2),YEAR(TODAY())&”/”&RIGHT(YEAR(TODAY())+1,2))

But I am also having to enter the data retrospectively and don’t want to have to calculate the academic year manually.

Do you not just need to close the AND argument with a bracket.

=IF(AND(R6 >= B1,R6 <= B2), R6, “”)

Thanks, it works!!!

Yeah Tuesday is Stoners day off. I’m the substitute excel helper so it’s not a problem.

So nesting?

This is what I’ve got:

=IF(AND(F2 >= $A$1,F2 <= $B$1), $C$1, “”),IF(AND(F2 >= $A$2,F2 <= $B$2), $C$2, “”),IF(AND(F2 >= $A$3,F2 <= $B$3), $C$3, “”),IF(AND(F2 >= $A$4,F2 <= $B$4), $C$4, “”),IF(AND(F2 >= $A$5,F2 <= $B$5), $C$5, “”),IF(AND(F2 >= $A$6,F2 <= $B$6), $C$6, “”),IF(AND(F2 >= $A$7,F2 <= $B$7), $C$7, “”)))))))

Showing FALSE, any ideas?

Or:

=IF(AND(D1 >=A1,D1 <B1), C1, “”), IF(AND(D2 >=A2,D2 <=B2), C2, “”), IF(AND(D3 >=A3,D3 <=B3), C3, “”), IF(AND(D4 >=A4,D4 <=B4), C4, “”), IF(AND(D5 >=A5,D5 <=B5), C5, “”), IF(AND(D6 >=A6,D6 <=B6), C6, “”), IF(AND(D7 >=A7,D7 <=B7), C7, “”)))))))

If you get, FALSE, then you have a missing set of speech marks

this?

=IF(AND(F2>=$A$1,F2<=$B$1),$C$1,IF(AND(F2>=$A$2,F2<=$B$2),$C$2,IF(AND(F2>=$A$3,F2<=$B$3),$C$3,IF(AND(F2>=$A$4,F2<=$B$4),$C$4,IF(AND(F2>=$A$5,F2<=$B$5),$C$5,IF(AND(F2>=$A$6,F2<=$B$6),$C$6,IF(AND(F2>=$A$7,F2<=$B$7),$C$7,””)))))))
Depends what you want as the default

=IF(AND(C1>=A1,C1<=B1),TEXT(A1,”YY”)&”/”&TEXT(B1,”YY”),””)

???

hey how do you get the whole statement in?

=IF(AND(F2>=$A$1,F2<=$B$1),$C$1,IF(AND(F2>=$A$2,F2<=$B$2),$C$2,IF(AND(F2>
=$A$3,F2<=$B$3),$C$3,IF(AND(F2>=$A$4,F2<=$B$4),$C$4,IF(AND(F2>=$A$5,F2<=$
B$5),$C$5,IF(AND(F2>=$A$6,F2<=$B$6),$C$6,IF(AND(F2>=$A$7,F2<=$B$7),$C$7,”
default”)))))))

That’s a hell of a long formula! You entering it as a nested function?

Ctrl+Shift+Enter?

Would this do it?

=IF(MONTH(A2)<4,YEAR(A2)-1&”-“&RIGHT(YEAR(A2),2),YEAR(A2)&”-“&RIGHT(YEAR(A2)+1,2))

I think I was repeating the default text…

TBH I’ve spent allday looking at excel and have drunk half a bottle of wine.

Just bang all your data in one spreadsheet. Apply filters and then use filter appropriate filters on the date columns. Even if you have 20 years of data you’ll be done in 10 mins max.

This works:

=IF(AND(F11>=$A$1,F11<=$B$1),$C$1,IF(AND(F11>=$A$2,F11<=$B$2),$C$2,IF(AND(F11>=$A$3,F11<=$B$3),$C$3,IF(AND(F11>=$A$4,F11<=$B$4),$C$4,IF(AND(F11>=$A$5,F11<=$B$5),$C$5,IF(AND(F11>=$A$6,F11<=$B$6),$C$6,IF(AND(F11>=$A$7,F11<=$B$7),$C$7,””)))))))

But can you only nest up to seven?

Would this be easier if I could change the crossover month to August/September:

=IF(MONTH(A2)<8,YEAR(A2)-1&”-“&RIGHT(YEAR(A2),2),YEAR(A2)&”-“&RIGHT(YEAR(A2)+1,2))

It’d be easy to do in Access or My SQL

=TEXT(VLOOKUP(C1,A:B,1),”YY”)&”/”&TEXT(VLOOKUP(C1,A:B,2),”YY”)

Start Dates in Col A
End Dates in Col B

Cell C1 contains the date you want to get the academic year from.

Cell D1 contains the above formula.
Enter a date in C1 and D1 will find the relevant date range in Col A/B and extract the academic yy/yy.

I think you need to be more precise about what you want. Then we can start from there. What is that bit about multiple values?

This seems to work: =IF(AND(F2>=$A$1,F2<=$B$1),$C$1,IF(AND(F2>=$A$2,F2<=$B$2),$C$2,IF(AND(F2>=$A$3,F2<=$B$3),$C$3,IF(AND(F2>=$A$4,F2<=$B$4),$C$4,IF(AND(F2>=$A$5,F2<=$B$5),$C$5,IF(AND(F2>=$A$6,F2<=$B$6),$C$6,IF(AND(F2>=$A$7,F2<=$B$7),$C$7,IF(AND(F2>=$A$8,F2<=$B$8),$C$8,IF(AND(F2>=$A$9,F2<=$B$9),$C$9,IF(AND(F2>=$A$10,F2<=$B$10),$C$10,IF(AND(F2>=$A$11,F2<=$B$11),$C$11,””)))))))))))

But I think that this may be easier:

=TEXT(VLOOKUP(C1,A:B,1),”YY”)&”/”&TEXT(VLOOKUP(C1,A:B,2),”YY”)

Start Dates in Col A
End Dates in Col B

Cell C1 contains the date you want to get the academic year from.

Cell D1 contains the above formula.
Enter a date in C1 and D1 will find the relevant date range in Col A/B and extract the academic yy/yy.

Thank you all very much for your patience 🙂

you can have any number of rows of date ranges with this method, only need to keep dates in col A in asc order for the lookup part of the formula to work correctly.

Basically I have records for areas covered which have unique record numbers. They have a start date, and an end date as to how long they took to cover. I am entering to data for 75 unique record going back ten years. I need a column which will show the academic year based on the start date for each individual record. I am using pivot tables to analyse the data once it has all been entered.

Thanks again 🙂

If I had the start date in F2 and the end date in G2 could I simply change the formula to:

=TEXT(VLOOKUP(F2,A:B,1),”YY”)&”/”&TEXT(VLOOKUP(F2,A:B,2),”YY”

Or would I need to have this?

=TEXT(VLOOKUP(F2,F:G,1),”YY”)&”/”&TEXT(VLOOKUP(F2,F:G,2),”YY”

Thanks

mostly this one:

=TEXT(VLOOKUP(F2,F:G,1),”YY”)&”/”&TEXT(VLOOKUP(F2,F:G,2),”YY”)

You would need to change F2 in the above formula to be the cell address of the date you want to look up, e.g H2

So Col F contains the start dates
Col G —–“—— end dates

Cell H2 contains the date you want to lookup
Cell I2 contains the formula:
=TEXT(VLOOKUP(H2,F:G,1),”YY”)&”/”&TEXT(VLOOKUP(H2,F:G,2),”YY”)

HTH

I can send an example S/S if you like?

Just use vlookup with the lookup type as TRUE, looking up a single column of dates (the ‘from’ column), with the lookup returning the academic year column.

If the vlookup cannot return a match, it returns the next smallest value, so in this case it will return the year from the period before.

Hey,

I think I have got it sorted 🙂

Thank you so much for all your help!!!

Cheers, Ben

wot ^ said. Just make sure you include start/end dates for all the acedemics years without breaks. For example, if you do not have start/end dates for year 05/06 and you want to look up a date that should be in that period then it might return 04/05 instead.