Forum Discussion

CharmaineSilver's avatar
CharmaineSilver
Qrew Member
5 years ago

Formula to convert part of number to date

Hi
In South Africa we have Identity numbers which are 13 digits, the first 6 digits are your date of birth eg ID number is  8404130028085, then date of birth is 13 April 1984.
What formula(s) can I use to extract the first 6 numbers - I tried - (Left, Field, 6), then add the century (ie 19, or 20 if the first 2 numbers are less than 20) and then convert it to a date. What type of field must the ID Number be? I tried number but then Left wouldn't work, changed to text but now just doesnt give an answer although it accepts the formula.  Also wht type of field must this date of birth be- I have it as formula- date.
Any help most appreciated.
Thanks

------------------------------
Charmaine Silverman
------------------------------
Formulas and functions
  • KristinTEST's avatar
    KristinTEST
    Qrew Member
    5 years ago
    I took a quick stab at this, but try this in your 'formula-date' field, and substitute the [ID#] with your corresponding ID # field. Someone may have a better solution for the year, as the formula below will only work up for years 1950-2049. 

    Lastly, if you want the date to be formatted correctly, with DD/MM/YYYY, you would need to use a 'formula-text' field with the ToFormattedText function.


    var text DOB = Left([ID#],6);
    var text Yr = Left($DOB, 2);
    var text Mo = Mid($DOB, 3, 2);
    var text CalDay = Mid($DOB, 5, 2);

    ToDate(List("-",
    $CalDay,
    $Mo,
    If(ToNumber($Yr)<=99 and ToNumber($Yr)>=50, 19 & $Yr, 20 & $Yr))) ​

    ------------------------------
    Kristin Seibert
    ------------------------------