cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KeerthiRaj96
Helper I
Helper I

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

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.

 

KeerthiRaj96_3-1631619901730.png

 

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

 

KeerthiRaj96_0-1631619539046.png

 

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

 

KeerthiRaj96_1-1631619746814.png

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!

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

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.



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

View solution in original post

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

KeerthiRaj96_0-1631784052761.png

 

 

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

View solution in original post

9 REPLIES 9
abm
Super User
Super User

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

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:

 

KeerthiRaj96_0-1631626508982.png

 

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?

KeerthiRaj96_1-1631627165832.png

Kindly help out.

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

 

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

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). 

 

 

KeerthiRaj96_0-1631636434452.png

 

I have inputted only one entry in excel file:

KeerthiRaj96_1-1631636679669.png

 

Could you please advise the reason for the same? 

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials
KeerthiRaj96
Helper I
Helper I

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.

KeerthiRaj96_0-1631721271997.png

 

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

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.



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

View solution in original post

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

KeerthiRaj96_0-1631784052761.png

 

 

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

View solution in original post

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (2,447)