Using 'mid' function to extract unusual date format
To Whom it May Concern,
I am currently trying to extract a date from the following ID format:
2720815240xxxx - the date in this ID is represented by the 2nd to 7th digits 72-08-15 (YY/MM/DD) August 15th 1972
3041009240xxxx - the date in this ID is represented by the 2nd to 7th digits 04-10-09 (YY/MM/DD) October 10th 2004
I was able to extract the relevant numbers via the following code: =mid([ID],2,6)
Following this step I was unable to obtain correct dates. I tried the following expression: =DATE(mid([ID],2,6),'YY/MM/DD')
and got results in date format that were incorrect. For example the above-mentioned 2720815240xxxx became 73/07/09.
Is there anyway to work with this format? basically anything beginning with 2 means the person was born in the 20th century, and anything beginning with 3 the 21st century. This is the Egyptian ID system.