[Solved] Id to date of birth

Discuss the spreadsheet application
Post Reply
Knippie
Posts: 4
Joined: Thu Feb 08, 2018 10:39 am

[Solved] Id to date of birth

Post by Knippie »

Hello I hope someone can help as Spreadsheets are not my strong suit, or something I do regularly.

I have a list of clients (644) I have all the id numbers but date of birth is compulsory for the program the info is imported into.
The date of birth format is crucial and needs to be yyyy-mm-dd
I want to "get" the date of birth from the id numbers.

I have looked all over and realise it has to do with left and mid functions but just cannot seem to get it right, can someone please assist?
Last edited by Knippie on Thu Mar 15, 2018 9:17 am, edited 2 times in total.
Libre Office 5.4 on Windows 8
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Id to date of birth

Post by RoryOF »

Are the dates of birth already entered on the sheets, but in incorrect format? If so, are they all in the same (incorrect) format? A sample of a few dates of birth in their (incorrect) format would be helpful.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Knippie
Posts: 4
Joined: Thu Feb 08, 2018 10:39 am

Re: Id to date of birth

Post by Knippie »

No I do not have the dates of birth yet just the id's but need it in format yyyy-mm-dd
Libre Office 5.4 on Windows 8
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Id to date of birth

Post by keme »

Do the IDs contain birth date in some encoded form?

IOW, are you asking about how to extract the date from that ID?

A Norwegian public ID (used for all official public services) consists of six digits for birth date - DDMMYY - followed by 3 digits serialized for births on that date (odd numbers for male, even for female), and a two digit checksum. 11 digits in all. Is this something like what you have? If so, give us a description of the encoding, perhaps with an example or two. Does not have to be a real ID, as long as it shows the format.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Id to date of birth

Post by RoryOF »

We need some sample data.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Knippie
Posts: 4
Joined: Thu Feb 08, 2018 10:39 am

Re: Id to date of birth

Post by Knippie »

Ok so I have this id for example: 7109080025085 and now with this function: =left(A2,2)&"-"&mid(A2,3,2)&"-"&mid(A2,5,2)
I managed to get the date to: 71-09-08 but I still need to ad the 19 to 71 to get this 1971-09-08

I realised that there must be a problem with my Office Calculator as I couldn't get the above function to give me a value but got it to work in Google spreadsheets.

Can you help me add the 19 as formatting the date doesn't help!?
Libre Office 5.4 on Windows 8
Knippie
Posts: 4
Joined: Thu Feb 08, 2018 10:39 am

Re: Id to date of birth

Post by Knippie »

No worries, I solved this by doing all my work in google Spreadsheets.
Seems there is something wrong with my Libre Office Calculator.
Libre Office 5.4 on Windows 8
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Id to date of birth

Post by RoryOF »

Try
="19"&LEFT(A1;2)&"-"&MID(A1;3;2)&"-"&MID(A1;5;2)

Note the semicolons as separators of the arguments in the Left and Mid functions.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Post Reply