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.

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (2)

Verified answer

David_MA 12,966 Super User 2025 Season 2 on at

Like (1)

Report
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:

Was this reply helpful? Yes No
amulhearn0207 109 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

wskinnermctc 6,519 Moderator on at

Like (1)

Report
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')

Was this reply helpful? Yes No
amulhearn0207 109 on at

Like (1)

Report

@wskinnermctc This is BEAUTIFUL! Thank you!

Was this reply helpful? Yes No
Kaif_Siddique 2,108 Super User 2024 Season 1 on at

Like (0)

Report
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:

Was this reply helpful? Yes No
David_MA 12,966 Super User 2025 Season 2 on at

Like (0)

Report

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 🙂

Was this reply helpful? Yes No