Re: Changing a column to date instead of text

Anonymous · ‎07-20-2020

Hello! I know this is not the right topic but I hope someone can help me, as the forum of Power Automate is very slow with replies.

I have a flow which will create a table of an excel file and then puts all of the information inside of a sharepoint list.

The excel file looks like this when it is being exported (It is not a table yet but does have the column name there already):

Column name (text)
Row (date)
Row (date)
Row(date)

So the column name is set to Text and the rows are a Date value. Whenever I use the function create table in my flow, it will turn the whole column into a text value as the column name is a text value instead of a date value.

How could I change my flow to turn it into a date column?

Create table function: https://gyazo.com/731188e6407c22e0521a4a81fd0d41cd
Creating the sharepoint list with the information of the excel file: https://gyazo.com/ed9853f60bd6b44d177119a993649b9a

Edit: Here is how it looks after the create table function has been finished. https://gyazo.com/3d498ea3137426345a6006a3ee533b3a

This shows that the column name is a text value and the rows are a date value.

jlindstrom · ‎07-20-2020

So why add data to excel just to then add it to a sharepoint list? Can’t you just use the create item step and create your list items directly without having to go to excel?

you can also ore create your spreadsheet in OneDrive with the desired table format and use the excel connector insert item step to add a record to the spreadsheet table

dates in flow are difficult. You can convert text to date using the formulas shown in this post thread.

https://powerusers.microsoft.com/t5/Building-Flows/Convert-string-to-date/td-p/289830

Anonymous · ‎07-20-2020

The Excel file is being exported from my Software and I need to use Sharepoint as a database for Power Apps because excel has limitations. @jlindstrom

I have tried this but it does not work:

formatDateTime('Start_x002d_Date','dd-MM-yyyy')

I still get this error:

Unable to process template language expressions in action 'Create_item' inputs at line '1' and column '2144': 'In function 'formatDateTime', the value provided for date time string 'Start_x002d_Date' was not valid. The datetime string must match ISO 8601 format.'.

The date is formatted like this in the Excel file: 2-7-2020 (dd-mm-yyyy)

jlindstrom · ‎07-20-2020

8601 is date format YYYY-MM-DD. Change the order of the date components

Anonymous · ‎07-20-2020

I am not able to change the date inside of my Excel file, can we change the order using the formula? @jlindstrom

jlindstrom · ‎07-20-2020

Yes, instead of this

formatDateTime('Start_x002d_Date','dd-MM-yyyy')

Do formatDateTime (‘Start_x002d_Date’,’yyyy-MM-dd’)

Anonymous · ‎07-20-2020

Getting this error then @jlindstrom

https://gyazo.com/c9f3ae7329f39e33263c530d4300d6b0

Anonymous · ‎07-20-2020

This is what happends. I export the Excel file from my software, at this moment, there is no table made.

The Start - Date column has 2 different formats. The text of the name of the of the column is set to text format and the other rows are date value, shown in this gif: https://gyazo.com/b961eb492e3ea9423e5d1d8638638bd6 (Top right)

I create a table using Power Automate and I think it is then a text column and not a date column. I then list all the rows inside of the excel table and create items inside of my SharePoint list. https://gyazo.com/5c3d537692129e43e2c35daa48de47fc

It then transfers the date, for example "2-7-2020" to "44014", why is this happening? The column inside of my SharePoint is set to Date

After the create table flow goes off: https://gyazo.com/0678c3ceee3a76317e85c6991ea5e9cd

It creates the table and these are the values (on the top right)

How come the value "2-7-2020" gets changed to "44014" when it goes from a text column to another text column?