help neded with formula

From 1770824062950 were 77 is year, 08 Aug, 24 day
Aplied formula
DATE(IF(OR(MID(c11,1,1)=“1”,MID(c11,1,1)=“2”),“19” & MID(c11,2,2),IF(OR(MID(c11,1,1)=“3”,MID(c11,1,1)=“4”),“18” & MID(c11,2,2),“20” & MID(c11,2,2))),MID(c11,4,2),MID(c11,6,2))
not working , to extract birthdate

Hi,

  1. Cell names are case sensitive in Ragic Formula, please use C11 instead of c11.

  2. To wrap a text, you should use double quote(") or single quote(’), not Quotation mark(“ ”).

  3. “IF(OR(MID(C11,1,1)=‘1’,MID(C11,1,1)=‘2’),‘19’ & MID(C11,2,2),IF(OR(MID(C11,1,1)=‘3’,MID(C11,1,1)=‘4’),‘18’ & MID(C11,2,2),‘20’ & MID(C11,2,2))),MID(C11,4,2),MID(C11,6,2)” return 40, which is not a valid parameter for DATE formula.

Hi,

If you need assistance in writing the formulas, please kindly share the logic of how to interpret the string into birthday as we do not quite understand your formulas.

Hi

What i understand is that the first digit of “1770824062950” determines the century.

1 or 2 means 1900
3 or 4 means 1800
everything else means 2000

Month is always on digit 4-5
Day is always on digit 6-7

To get the string Day.Month.Year (you can assign it to date field - based on your date format)
MID(C11,5,2)+’.’+MID(C11,3,2)+’.’+IF(LEFT(C11,1)<3,‘19’,IF(LEFT(C11,1)<5,‘18’,‘20’))+MID(C11,1,2)

This should do the trick.
Oliver

Hi Oliver,
in This Case, The number represents a unique ID number for Romania. 13 digits
The first number represents the Birth Year and the gender
for year 19xx 1=Male - 2 = Female
for year 20xx 5=Male - 6 = Female
Second and Third The XX last digits of Year
Fourth and fifth represents the birth month
then 6-7 the birthday
The last 6 digits represent a code for the region and urban or rural. Not important.
I need to extract

  • the gender in 1 cell = Male or Female
  • Age in the second Cell ( years and months)

Please a suggestion formula