Forum Discussion
ID School Year in new cell based on full date field
I have a field with a course_start_date. I would like to create a new column for identifying school year based on a few month/year conditions. Can you help me write the correct formula?
1. If the month and year falls between 07/01/2022-06/30/2023 in A2 (A2 Ex.11/13/2021), then make B2 say, "SY 2022-2023", If the month and year falls between 07/01/2021-06/30/2022 in A2, then make B2 say, "SY 2021-2022"...include a few more timeframes, but same idea.
2. Separately, could I use slicers or another Excel tool to help with this task of defining the school year? (I still want to know how to set up the formula, but would love to learn other ways too.) Thanks!
1 Reply
Use
="SY "&YEAR(EDATE(A1,-6))&"-"&YEAR(EDATE(A1,-6))+1
If you have Microsoft 365 or Office 2021, you can also use
=LET(Yr,YEAR(EDATE(A1,-6)),"SY "&Yr&"-"&Yr+1)
