Populate Date Column in SharePoint using String

(0) Share

Report

Posted on by amulhearn0207

109

Hello.

I have a text field in a SharePoint list formatted as: AAA:20230114, where the text to the right of the colon (:) represents a date - in this example, January 14, 2023.

How can I manipulate that text and make it populate a date column in a SharePoint list?

Thank you.

All responses (6)

Answers (2)

David_MA 11,517 Super User 2025 Season 1 on at

Like (0)

Report

Re: Populate Date Column in SharePoint using String

Nice! I always have a hard time combining the expressions when it gets to be long. I always mess up the placement of a comma or miss or add too many parentheses 🙂
Kaif_Siddique 2,104 Super User 2024 Season 1 on at

Like (0)

Report
Re: Populate Date Column in SharePoint using String

Here is a sample flow. Customize as per your flow.

formatDateTime(concat(substring(last(split(outputs('Compose'),':')),4,2),'/',substring(last(split(outputs('Compose'),':')),6,2),'/',substring(last(split(outputs('Compose'),':')),0,4)),'MM/dd/yyyy')

Output:
amulhearn0207 109 on at

Like (1)

Report

Re: Populate Date Column in SharePoint using String

@wskinnermctc This is BEAUTIFUL! Thank you!
Verified answer

wskinnermctc 6,517 Super User 2025 Season 1 on at

Like (1)

Report
Re: Populate Date Column in SharePoint using String

Dang, I'm late because I was making screenshots. However, I do have a way to get them in a single expression.

Example FlowExample Flow Results1Example Flow Results 2

The above flow is examples that were in general.

Below is for a single expression.

Below is the full expression:

formatDateTime(concat(substring(last(split(variables('FullTextWithDate'), ':')), 0, 4), '-', substring(last(split(variables('FullTextWithDate'), ':')), 4, 2), '-', substring(last(split(variables('FullTextWithDate'), ':')), 6, 2)), 'yyyy-MM-dd')
amulhearn0207 109 on at

Like (0)

Report

Re: Populate Date Column in SharePoint using String

Thank you, this is excellent, as my items are always formatted identically to the sample.
Verified answer

David_MA 11,517 Super User 2025 Season 1 on at

Like (1)

Report
Re: Populate Date Column in SharePoint using String

Your content is always consistently formatted the way you show it? If so, you can use some expressions to generate it in the format needed to populate the SharePoint date field:

I created a manually triggered flow to test it on your format. The first expression takes your value and splits it on the semicolon and gets the last part (the number date):

last(split(triggerBody()['text'],':'))

The next expression concatenates the number to format as an ISO8601 date (yyyy-MM-dd):

concat(substring(outputs('Compose'),0,4),'-',substring(outputs('Compose'),4,2),'-',substring(outputs('Compose'),6,2))

The last compose is just to illustrate that it recognizes Compose 2 output as a valid ISO8601 date:

formatDateTime(outputs('Compose_2'),'D')

Which results in this from your example: