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 ...
Forum Discussion
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
------------------------------
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
------------------------------
