Using 'mid' function to extract unusual date format

[malradi88]

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. 

Thank you for your support!

Best,

Mohammed

[millerhm · Accepted solution]

well, hey - you learn something new every day! Thanks for telling us about the date Egyptian format.

I think you likely need to tell Qlik that the value you're extracting is a date before formatting it. Have you tried 

=DATE(Date#(mid([ID],2,6),'YYMMDD'),'YY/MM/DD')

That should tell Qlik that the value you've extracted is a date in the format YYMMDD and you want it to then be displayed as YY/MM/DD'. 

[Kushal_Chawda · Accepted solution]

You need to provide the same format as is using date# to recognize it as Date and then use Date function on top of that

date(DATE#(mid([ID],2,6),'YYMMDD'),'YY/MM/DD')

[malradi88]

Thank you very much! 

[malradi88]

Thank you very much!