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)

Sort by

Verified answer

David_MA 14,788 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
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
Copy link

Link copied!

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
Copy link

Link copied!
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
Copy link

Link copied!

@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
Copy link

Link copied!
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 14,788 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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