Trying to calculate a school year based on month/year

Kevin7859

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.

KPH

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 January-June 2019 is school year 2018-2019 and July-December 2019 is 2019-2020, 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 18-19, 19-20, etc. let me know and I will send a revised formula that uses only the right most characters.

Kevin7859

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.

KPH

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 Jan-June 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)

KPH

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.

Kevin7859

I ended up using another table with the month and years and associated SY from July-June and then used index match. thanks for the help.