Trying to calculate a school year based on month/year
I am trying to create a formula where it looks at the month and year and then return the school year.
if(AND(Month@row=Month in table, Year@row=year in table), School year, "")
The school year is July through June.
Any help is greatly appreciated.
Answers

Hi @Kevin7859
I am not sure what the table is showing. Is this the result you want? It doesn't match the July through June rule.
Do you want to be able to enter a month and year into two separate columns and for the School Year to populate in a new column on the same row?
Would it be acceptable to express the school year in the same format as the Year columns? i.e. yyyy rather than yy? That will simplify the formula.
You can create an IF function that looks at the month and returns either the current year minus one then a hyphen then the current year, or returns current year then hyphen then current year plus one.
I have assumed JanuaryJune 2019 is school year 20182019 and JulyDecember 2019 is 20192020, so am checking is the month is less than July.
That formula is:
=IF(Month@row < 7, (Year@row  1) + "  " + (Year@row), (Year@row) + "  " + (Year@row + 1))
The output looks like this:
Is that what you are after?
If you want to remove the 20s from the dates so they are 1819, 1920, etc. let me know and I will send a revised formula that uses only the right most characters.

The table was my reference table to look at for the criteria of the formulas. Was trying to avoid having to do a row lookup and use more of a pivot matrix.

Hi Kevin
If that table is the output that you want then the formula above will not work for you. Below is what the result would be for the dates in your table. For JanJune the school year is the previous year to the current. In your table it is the current year to the next.
It looks like maybe you don't want to consider the month and the school year is simply year minus one to current year. You can do that using:
= (Year@row) + "  " + (Year@row + 1)

Sorry Kevin, I didn’t see that you’d edited the comment until after I posted my response.
Can you let me know if the formula is what you need? If not, please share a screenshot of what your input looks like and what you want to return.
If you would prefer to create a matrix in another sheet and reference that, you can use an INDEX function. But that will mean you need to keep the matrix up to date. Let me know if that is what you need.

I ended up using another table with the month and years and associated SY from JulyJune and then used index match. thanks for the help.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!