Decade Crossover and Reset

[crazybake]

Our institution assigns numbers to represent our quarter and year, "672", for example. The right number is the quarter (1-4, where summer is 1, fall is 2, winter is 3, spring is 4); and the left two numbers represent the year. In this case, "672" would equal Fall 2016.

When student enters program (e.g, 672), it is entered into Quarters::CoreNumberCalc 
Then converted into plain text "Fall 2016"using calculations :
Case ( Right ( CoreNumberCalc ; 1 )  = 1; "Summer";Right ( CoreNumberCalc ; 1 )  = 2; "Fall"; Right ( CoreNumberCalc ; 1 )  = 3;"Winter";"Spring")
Case ( Right ( CoreNumberCalc ; 1 ) <= 2; "201" & Left ( CoreNumberCalc ; 1 );"201" & Middle ( CoreNumberCalc ; 2;1 ))

I have a secondary calculation field that will auto-calculate the institutional number for graduation (Quarters::CoreNumberCalc=  672, then graduation would be 783, or "Winter 2018") - if students start in spring, a gap in graduation occurs: 
Quarters::GraduationNumberCalc = If ( Right ( CoreNumberCalc ; 1 ) < 4;  CoreNumberCalc + 111 ; CoreNumberCalc + 218 )

 

...But, this would only be good for one decade at a time. If a student enters in 892 (Fall 2018), they would be projected to graduate in Winter 2020, and would have an institutional graduation number of "903"; if I use my calculations above, Quarters::GraduationNumberCalc=  "1003". How do I generate a calculation that accounts for each decade starting at '011' or crosses over decades like above?

[comment]

I cannot figure out what your question is. Do you want to calculate the year and the quarter from 1003? For that, you could use =

Let ( [
y = Div ( CoreNumberCalc ; 100 ) ;
q = Mod ( CoreNumberCalc ; 10 ) 
] ;
Choose ( q ; "" ; "Summer" ; "Fall" ; "Winter" ; "Spring" )
& " " &
2010 + y + ( q > 2 )
)

This will return "Winter 2021".

BTW, the second digit from right in your "CoreNumberCalc" is redundant. I don't know how much control you have over that, but it's a very clumsy code.

Edited March 10, 2017 by comment

[crazybake]

I am not sure if it is redundant or not. "67X" would mean that it includes Summer 2016, Fall 2016, Winter 2017, Spring 2017- thus the 67X. I have never questioned this...

To shed more light. The college actually adds an alpha character for each decade. For example 2000-01/2009-10 would add an A at the beginning (e.g., A672 for Fall 2006 or A673 for Winter 2007) for the next decade 2010-11/2019-20 they would add B (e.g. B672 for Fall 2016 and B673 for Winter 2017).

5 hours ago, comment said:

I cannot figure out what your question is. Do you want to calculate the year and the quarter from 1003? For that, you could use =

I am not sure. The numbers are reused every decade, and maybe that is my question. I don't know if I need to "reset" the calculation number, or create a a field that is a running total.

I don't know if that provides any more information.

Edited March 10, 2017 by crazybake
typo

[comment]

2 hours ago, crazybake said:

I am not sure if it is redundant or not.

It is redundant, because we know that after 6 comes 7. You can see that my calculation returns the correct results without using it at all; you will get the same result from "672" as you will from "602" or "612" or "622" and so on.

 

2 hours ago, crazybake said:

The college actually adds an alpha character for each decade.

That is the smart thing to do. If you only recycle the same codes every decade, you will encounter an ambiguity every time you are near a decade boundary. This is assuming you have something to look at in order to resolve the ambiguity; if all you have is the digits (e.g. "672"), you have no way to determine the decade.

 

2 hours ago, crazybake said:

maybe that is my question.

I suggest you formulate your question in the form of "this is what I have" (i.e. the input to the calculation) and "this is what I would like to have" (i.e. the required output).