RE: Date Format issue - Import excel to SharePoint list using Power Automate

[KeerthiRaj96]

Hi Folks,

I am building one flow using power automate to import excel template into the Sharepoint list.  My current Flow structure for your reference.

once the flow runs complete, I am receiving the below error with flow failed status as shown below:

I have one date field in the excel template where the column value will be in this format: "Jan-21". I have passed the column value via variables. I have Assigned the date field in the variable and used that variable into the flow to pass it.

Within create Item action, I have used this formula as an expression for the date field -> "Period End Field":

formatDateTime(variables('Periodend','mm-yyy'))

Kindly suggest your comments, whether this formula, causing the first screenshot error and flow fails?.

If yes, please share your comments for the date filed format - > Jan-21.

Thanks for your help in Advance!

[abm]

Hi @KeerthiRaj96 

Couple of things to check

1. Excel List Rows Present in a table. Expand the advanced options and set the Date Format to ISO 8601.

2. Check your Excel Date column data type is set as Date. 

3. Finally under the Create Item SharePoint use the FormatDateTime('yourexceldate', 'yyyy-MM-dd')

Thanks

[KeerthiRaj96]

Hi @abm,

Thanks for your response. As per your comments, I have made all the changes in the flow and set the column datatype as the date in excel, and ran the flow.

Still, I am facing the same error:

Used your formula and just edited the date field to my variable name : FormatDateTime(variables('Periodend'),'yyyy-MM-dd').

 FYI. I have the date field in the excel template where the column value will be in this format: "Jan-21" . please consider it.

 Please have a look at the settings of this date field in the SharePoint list. Should I make any changes to the date settings in the SP list?

Kindly help out.

[abm]

Hi @KeerthiRaj96 

Looks like your excel sheet is still giving the date as integer. Use the below expression

addDays('1899-12-30',int(item()?['DateOfBirth']),'dd/MM/yyyy')

Replace the item()?['DateOfBirth'] to map your excel sheet column name.

Thanks

[KeerthiRaj96]

Hi @abm,

My Requirement got changed now. Period End field value is said to be in "DD/MM/YYYY" format.  Also, I just applied the text datatype in excel and the same as SharePoint list Date field as text datatype. Also, removed the variables which i used earlier.

My flow works fine without errors, but in  Sharepoint, list post flow run, I could see 2 to 3 line items with the blank rows are being created along with my whole excel row (input which i have given). 

I have inputted only one entry in excel file:

Could you please advise the reason for the same? 

[abm]

Hi @KeerthiRaj96 

Check you Excel table have any blank rows. Also look the flow history run and expand the steps after reading the Excel. Compare against your Excel table/Rows.

Thanks

[KeerthiRaj96]

Hi @abm,

I have checked the flow as well as the excel which has blank rows and corrected it. My flow works fine.

I am importing an excel sheet into the SharePoint list (InfoPath form).  Currently, the list supports when I give the data format in excel as  9/14/2021 and data type -> text -> Flow works fine without issues.

when I change it to  14/9/2021 in excel and updated the flow with date field using

formula ->FormatDateTime(variables('VarDate'), 'dd-MM-yyyy') , it throws an error - 'item/period_start_date' is required to be of type 'String/date'. The runtime value '"14-09-2021"' to be converted doesn't have the expected format 'String/date'

Seems Infopath supports only mm/dd/yyyy format.

What I need is dd/mm/yyyy format ->14/09/2021. Is that possible to this format through the flow?

[abm · Accepted solution]

Hi @KeerthiRaj96 

Use the format date in flow as 'yyyy-MM-dd'. Also make sure Excel List Rows Present in a table set the Date Format to ISO 8601.

[KeerthiRaj96 · Accepted solution]

Hi @abm,

I have checked and already action -> Excel List Rows Present in a table is set the Date Format to ISO 8601.

As you told, I have changed the format date formula to -> FormatDateTime(variables('VarDate'), 'yyyy-MM-dd'. But still, I am facing this error ->In function 'FormatDateTime', the value provided for the date-time string '14/09/2021' was not valid. The DateTime string must match ISO 8601 format.

As I already mentioned, I have created this date-time picker field -> Period End using InfoPath. Infopath  date-time field properties show below  (format will be the same as shown above):

Whether the issue is with date format ->14/09/2021 or InfoPath DateTime picker formatting. Please help out!

[abm]

Hi @KeerthiRaj96 

Looks like you need to convert that into yyyy-MM-dd format. Since it receiving it as string you may need to look options for expression such as split with slash ('/') as delimiter to reformat that date in Power Automate.

Thanks