F
Fitzwilliam Darcy
I am trying to take a four digit code that we enter into a spreadsheet
column and copy it over to another column using substitution.
When I type 1902, for example, I would like Excel to decode the
information in another column. In this case, when I type 1902 it
should put 1990 Fall in the other column. This way we can see the year
and semester/quarter, but still use the code to sort into the proper
timeline.
The first three digits indicate the year, and the fourth digit
indicates the semester.
Fourth Digit
1 = summer
2 = fall
3 = winter
4 = spring
5 = summer
NOTE: the reason there are two summers is that we are accounting for
both the quarter and semester systems.
First Digit codes the first two numbers in the year
1 = 19
2 = 20
Second and third digit is the decade
So again, 1902 equals 1990 fall
2031 equals 2003 summer
I was playing with SUBSTITUTE(LEFT(B10,1),"1","19") which would take
the first digit if it is a 1 and convert it into 19. A lot more would
be required to parse the code, of course.
Now here is the tricky part!!!
If the fourth digit code is 3, 4, or 5 (winter, spring, or summer)
then the formula must add one to the decade.
Examples:
2031 equals 2003 summer
2032 equals 2003 fall
2033 equals 2004 winter (note that it is 2004 not 2003)
2034 equals 2004 spring (note that it is 2004 not 2003)
2035 equals 2004 summer (note that it is 2004 not 2003)
The above example is the 2003-2004 academic year. This four digit code
allows you to sort all of a student's academic record (from various
universities using both quarter/semester) in sequence.
So if any of you mavens can come up with a formula that can convert
the code into the actual semester, I would be exceedingly grateful!
Thank you!
column and copy it over to another column using substitution.
When I type 1902, for example, I would like Excel to decode the
information in another column. In this case, when I type 1902 it
should put 1990 Fall in the other column. This way we can see the year
and semester/quarter, but still use the code to sort into the proper
timeline.
The first three digits indicate the year, and the fourth digit
indicates the semester.
Fourth Digit
1 = summer
2 = fall
3 = winter
4 = spring
5 = summer
NOTE: the reason there are two summers is that we are accounting for
both the quarter and semester systems.
First Digit codes the first two numbers in the year
1 = 19
2 = 20
Second and third digit is the decade
So again, 1902 equals 1990 fall
2031 equals 2003 summer
I was playing with SUBSTITUTE(LEFT(B10,1),"1","19") which would take
the first digit if it is a 1 and convert it into 19. A lot more would
be required to parse the code, of course.
Now here is the tricky part!!!
If the fourth digit code is 3, 4, or 5 (winter, spring, or summer)
then the formula must add one to the decade.
Examples:
2031 equals 2003 summer
2032 equals 2003 fall
2033 equals 2004 winter (note that it is 2004 not 2003)
2034 equals 2004 spring (note that it is 2004 not 2003)
2035 equals 2004 summer (note that it is 2004 not 2003)
The above example is the 2003-2004 academic year. This four digit code
allows you to sort all of a student's academic record (from various
universities using both quarter/semester) in sequence.
So if any of you mavens can come up with a formula that can convert
the code into the actual semester, I would be exceedingly grateful!
Thank you!