So, I have what "might" be a very simple formula, or not 😞
At our college, terms are indicated as follows:
[academic][term number]
For example, 202201 would be Fall 2021. 202202 would be Winter 2021/22. 202203 would be spring 2022 and 202204 would be summer 2022. Again, this is used to gauge academic years rather than the calendar. The 02 term is really a write-off, as no one uses it. Its about 6 weeks long and overlaps with fall and spring.... The other three terms are four months each:
September to December is term 01
Jan to April is term 03
May to August is term 04
We have a master's degree program where students enroll for six terms. For example, they might start in 202403 and sequence through 202404, 202501, 202503, 202504, and 202601. Thus, starting in the Spring of 2024 and Graduating in the Fall of 2025.
I've found a way to do things like determine the last term based on the first term:
IF(
RIGHT([Cohort ID], 2) = "01",
REPLACE(CONCAT(TEXT(NUMBER(LEFT([Cohort ID], 4),0) + 1),"04"),",",""),
IF(
RIGHT([Cohort ID], 2) = "03",
REPLACE(CONCAT(TEXT(NUMBER(LEFT([Cohort ID], 4),0) + 2),"01"),",",""),
IF(
RIGHT([Cohort ID], 2) = "04",
REPLACE(CONCAT(TEXT(NUMBER(LEFT([Cohort ID], 4),0) + 2),"03"),",",""),
CONCAT(LEFT([Cohort ID], 4), "03")
)
)
)
Now, I need a way to identify a number (1-6) to let me know their progress in the program. For example if they are in their second term, I would need the number 2. If they were in their last semester I would need a 6, etc..... Its just beyond what I can figure out.
I tried feeding Claude AI all of the functions for smartsuite as a PDF and seeing what it could make of this. It gave me the following, but is erroring out.
To calculate the current semester number (1-6) based on the student's start term ([Cohort ID]), anticipated graduation term ([Anticipated Graduation Term]), and the current date (NOW()), you can use the following formula:
FLOOR((YEAR(NOW()) - LEFT([Cohort ID], 4)) * 3 + FLOOR((MONTH(NOW()) - 1) / 4, 1) - MID([Cohort ID], 5, 2) / 2 + 1.5, 1)
Here's how the formula works:
(YEAR(NOW()) - LEFT([Cohort ID], 4)) * 3: This calculates the number of semesters passed since the start year by subtracting the start year from the current year and multiplying by 3 (since there are 3 semesters per year).
FLOOR((MONTH(NOW()) - 1) / 4, 1): This calculates the number of semesters passed in the current year by subtracting 1 from the current month (to adjust for 0-based indexing) and dividing by 4 (since each semester is 4 months long).
MID([Cohort ID], 5, 2) / 2: This extracts the start semester number (01, 03, 04) from the [Cohort ID] and divides it by 2 to convert it to the number of semesters passed at the start (0 for fall, 1 for spring, 2 for summer).
1.5 is added to adjust for the fact that the student is considered to be in the next semester after the midpoint of the current semester.
FLOOR(..., 1) rounds down the result to the nearest integer to get the current semester number.