Hello, i have a problem with creating a SSN control check. So the formula is as described below.
I want to have a function that can control these four types of SSN
YYMMDD-XXXX (with hyphen)
YYMMDDXXXX (without hyphen)
What i want is for the formula below to check if the controlnumber (last X) is correct or not
by giving me a 1 if correct and 0 if false.
Basically you get a Control number (the last X of the four Xs).
eg. like this: 8 (*2) + 9 (*1) + 1(*2) + 1(*1) + 1(*2) + 0(*1) - X(*2) X(*1)X(*2) X (birthdate 891110-XXXX)
so that gets 16 + 9 + 2 + 1 + 2 + 0 + X(*2) X(*1)X(*2) = Last X (controlnumber)
The Control number (last X) is now the value you must add to the digit sum for it to be evenly divisible by 10. If your digit sum is 21, you must add 9 for it to be 30, which is divisible by 10.
so 9 is the Control number (last X in SSN) because that what i have to add to get evenly devisible 30 (21 + 9 = 30).
Eg. so if your SSN is 891110 - 1526 its wrong which my function should show (0)
but if my SSN is 891110 - 1529 its right according to my function which should show (1)
ive tried by going by '[0-9][0-9][0-9][0-9][0-9][0-9][0-9] and also tried different DATE forms but its hard getting a formula done for me which controls the whole birthdate to get the right controlnumber.
I also have a hard time getting past the hyphen.
I hope you guys get it, and also thank you in advance :)