When I utilize a flow to import excel table to a list , an error occurs. "STRING was not recognized as a valid datetime"

(0) Share

Report

Posted on by GUOGUO

389

Hi All ,

When I utilize a flow to import excel table to a list , an error occurs as below message.

The format of the column in excel as below:

the format or sharepoint list column as below:

Thanks a lot

Categories:

General topics

I have the same question (0)

All responses (11)

Answers (2)

eric-cheng 5,171 on at

Like (0)

Report
Copy link

Link copied!
Hi @GUOGUO ,

Please refer to this post

formatDateTime(addDays('1900-01-01',add(int(items('Apply_to_each')?['Date']),-2)),'yyyy-MM-dd')

Thanks

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Was this reply helpful? Yes No
GUOGUO 389 on at

Like (0)

Report
Copy link

Link copied!

Hi Eric
Thanks a lot.
Though, how should I use the expression to convert the column that I get from Excel table.

Was this reply helpful? Yes No
Verified answer

eric-cheng 5,171 on at

Like (1)

Report
Copy link

Link copied!

Hi @GUOGUO ,

1. Clear out the field
2. Click on the field
3. Click on the 'expression' tab in the flyout
4. Paste the below in and replace xxxx with the column name in Excel. If you renamed your Apply to each loop, you will need to change that too.

formatDateTime(addDays('1899-12-30', int(items('Apply_to_each')?['xxxx'])),'MM/dd/yyyy')

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Was this reply helpful? Yes No
GUOGUO 389 on at

Like (0)

Report
Copy link

Link copied!

Hi Eric .

Thanks for your solution. Though , it doesn't work. Could you please help to check ?

Was this reply helpful? Yes No
eric-cheng 5,171 on at

Like (0)

Report
Copy link

Link copied!

Hi @GUOGUO ,

Can you please take a screenshot of the loop from your get items and confirm the column from your excel is LIB Global Item Status? If possible, please add an output of this field and share the value.

Was this reply helpful? Yes No
GUOGUO 389 on at

Like (0)

Report
Copy link

Link copied!

Hi Eric
Yes，the Column in Excel named as

Was this reply helpful? Yes No
eric-cheng 5,171 on at

Like (0)

Report
Copy link

Link copied!

Hi @GUOGUO ,

I am not sure why you have the 'Apply To Each 3' loop. You should be able to move the 'Create Item' action out and into 'Apply To Each 2'. Then update your expression to:

Apply_to_each_2

You can try updating the expression first and see if it works too but looks to be alot of actions which are required.

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Was this reply helpful? Yes No
GUOGUO 389 on at

Like (0)

Report
Copy link

Link copied!

Thanks a lot ，i will try

Was this reply helpful? Yes No
Verified answer

eric-cheng 5,171 on at

Like (1)

Report
Copy link

Link copied!

Hi @GUOGUO ,

I build a similar flow so you can copy it.

1) Excel with datetime column

2) Get Tables and then List Rows Present In Table

3) For each row, Parse JSON value and then use the below formula

formatDateTime(addDays('1899-12-30', int(items('Apply_to_each_2')?['LIB Global Item Status'])),'MM/dd/yyyy')

4) Finished result

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Was this reply helpful? Yes No
GUOGUO 389 on at

Like (0)

Report
Copy link

Link copied!

Thanks a lot ,Eric

Was this reply helpful? Yes No