cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Laela
Frequent Visitor

Calculate Date of Birth from ID number column in a SharePoint List

Hi there, 

 

I was wondering if it would be possible to calculate Date of Birth from an ID number column in a SharePoint List?

 

The ID number column has the first 6 digits as the person's date of birth i.e. if the ID number is "920320 0003 084", the date of birth would be 20/03/1992 or 20 March, 1992. 

 

 

Kind regards, 

Laela. 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Laela ,

Do you want to get the birth date from entering number?

Could you tell me where do you get the year 1992 in your example? from "92"?

If so, do you mean that you set the year as "19xx" by default?

If so, I suggest you set the DatePicker's DefaultDate:

Text(Concatenate("19",Left(TextInput4.Text,6)),"[$-en-US]yyyy/mm/dd")

On your side, you may need this:

 

Text(Concatenate("19",Left(DataCardValue41_1.Text,6)),"[$-en-US]yyyy/mm/dd")

//then you will get the birth date in the format of the datepicker's default format.

Since you want this format: "dd/mm/yyyy", so please set the datepicker's Format:

"dd/mm/yyyy"

3400.PNG

 

Then you will get the birth date in the format as you want.

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
eka24
Super User
Super User

Insert a textbox and put this formula:
Text(Left(Textbox1.Text,6),"dd-mmm-yyyy")

Where Textbox1 is where the ID column is entered
Laela
Frequent Visitor

Hi @eka24 ,

 

Thank you for your reply. 

 

I added the formula to the date field that I had for Date of Birth and this is what it gave me:

 

Screenshot 2020-03-04 at 09.19.25.png

 

This is the formula I had for the Date of Birth text input: 

Text(Left(DataCardValue41_1.Text,6),"[$-en-GB]dd-mmm-yyyy") where "DataCardValue41_1" was the RSA ID Number text input. 

 

It doesn't seem to be displaying correctly - please let me know how I can fix this?

I am using a date field for date of birth because I need to use it to calculate age in another SharePoint calculated column. 

 

Kind regards, 

Laela.  

Hi @Laela ,

Do you want to get the birth date from entering number?

Could you tell me where do you get the year 1992 in your example? from "92"?

If so, do you mean that you set the year as "19xx" by default?

If so, I suggest you set the DatePicker's DefaultDate:

Text(Concatenate("19",Left(TextInput4.Text,6)),"[$-en-US]yyyy/mm/dd")

On your side, you may need this:

 

Text(Concatenate("19",Left(DataCardValue41_1.Text,6)),"[$-en-US]yyyy/mm/dd")

//then you will get the birth date in the format of the datepicker's default format.

Since you want this format: "dd/mm/yyyy", so please set the datepicker's Format:

"dd/mm/yyyy"

3400.PNG

 

Then you will get the birth date in the format as you want.

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yutliu-msft,

 

Thank you so much, I entered this formula and it works perfectly!

 

Text(Concatenate("19",Left(DataCardValue41_1.Text,6)),"[$-en-US]yyyy/mm/dd")

 

 

This is my result:

Screenshot 2020-03-04 at 11.15.15.png

 

Thank you so much for your help!

 

Kind regards, 

Laela. 

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (2,739)