Date in excel as dd-mm-yyyy covert to SharePoint

[Anonymous]

Hi Team,

Need your help for one of my workflow which is used for bulk upload of data to SharePoint, i have a excel sheet which contains data includes date in 4 different columns, however the default format in the excel includes the date format in "DD-MMMM-YYYY", unfortunately the SharePoint throws an error as date string not recognized in that format when the flow is run, i have tried to change the date format to "MMMM/DD/YYYY" which works fine, since my bulk load has data which has the format to maintain as "dd-mm-yyy" can some one please help me a way to have it worked if the default dates are in the format DD-MMMM-YYYY format

[gabibalaban]

@Anonymous ,

Can you please show us how you built the flow ? 

I guess you can select the DateFormat from this action 'List_rows_from_a_table' to Number and made the conversion inside the flow using:

AddDays('1899-12-30',int(body('List_rows_from_a_table')['value']['DateColumn']),'dd-mm-yyyy')

or if you want to keep the DateFormat to ISO8601 you can use:

formatDateTime(body('List_rows_from_a_table')['value']['DateColumn'],'dd-mm-yyyy')

Hope it helps !

[Anonymous]

   Thank you for your response @gabibalaban , i saw this formulae in many forum but i am not sure how to apply that in my workflow, i have attached the workflow for your reference if you could help me how and where to place the date format in the power automate workflow, below is the format of the excel how the date format is updated, thanks for all your help

[gabibalaban · Accepted solution]

@Anonymous ,

Let try option two:

1.Expand the Show advanced option of the List rows from a table action and select dateformat as ISO8601.

2. Then on Sharepoint - create item -> date of birth, use with the help of add dynamic content expression tab:

 formatDateTime(items('Apply_to_each')['Date of Birth'],'dd-MM-yyyy')

For option one (date as number) is the same logic:

1.Expand the Show advanced option of the List rows from a table action and select dateformat as Number.

2. Then on Sharepoint - create item -> date of birth, use with the help of add dynamic content expression tab:

AddDays('1899-12-30',int(items('Apply_to_each')['Date of Birth']),'dd-MM-yyyy')

Hope it helps !

[Anonymous]

I tried both the steps i get the warning as below

The 'inputs.parameters' of workflow operation 'Create_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/DateofBirth' is required to be of type 'String/date'. The runtime value '"01-00-1983"' to be converted doesn't have the expected format 'String/date'.

[gabibalaban]

Oups ...sorry. This is powerAutomate 🙂 ... MM instead of mm for month. I corrected my post.

Also from my knowledge for sharepoint you need this format "yyyy-MM-dd". Please try it if the previous format doesn't work.

[Anonymous]

Thanks @gabibalaban you are a star it actually worked, just one more question so i have dates to be entered in three other columns in that table so i just have to change date of birth with the column names in that formulae right?

[gabibalaban · Accepted solution]

Yes ... the items('Apply_to_each') actually contains the entire current row, so what you need to do is to refer to the right column name.

If my answer helped you to solve the issue please don't forget to mark the solution, maybe other user will face the same problem.

Thx.  

[Anonymous]

i have one small doubt @gabibalaban while i do the bulk upload even if the format in the excel is of same format for all i see while running after 2 mins of run it shows a error like below

The 'inputs.parameters' of workflow operation 'Create_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/DateofBirth' is required to be of type 'String/date'. The runtime value '"18-01-1993"' to be converted doesn't have the expected format 'String/date'.

but few of the data gets added to the sharepoint but after 2 mins of run it states the above error, any fix for this or am i missing someting here  

[gabibalaban]

@Anonymous,

As i wrote in a previous post, i suspect that the date format in unusable for sharepoint which expect ‘yyyy-MM-dd’ or ‘MM-dd-yyyy’.

Give it a try.