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?
[Solved] Id to date of birth
[Solved] Id to date of birth
Last edited by Knippie on Thu Mar 15, 2018 9:17 am, edited 2 times in total.
Libre Office 5.4 on Windows 8
Re: Id to date of birth
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
Re: Id to date of birth
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
Re: Id to date of birth
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.
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.
Re: Id to date of birth
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!?
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
Re: Id to date of birth
No worries, I solved this by doing all my work in google Spreadsheets.
Seems there is something wrong with my Libre Office Calculator.
Seems there is something wrong with my Libre Office Calculator.
Libre Office 5.4 on Windows 8
Re: Id to date of birth
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.
="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