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

Problem copying date/time value from Excel to SharePoint list

I'm having an issue with copying a date/time value from an Excel file to a SharePoint list. The input format from Excel is yyyy-MM-dd hh:mm and when I create the item in the SharePoint list, I instead get an integer value.

 

I cannot figure out the expression to get to get this conversion to work. I've tried the addToTime expression, thinking I could add the integer (in days) to the date 1900-01-01 (which seems to be where I end up if I subtract the integer I get from the date that I wanted).

 

Any help is appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @RobElliott ,

 

Please take a try wo create the flow as below:

1.png

 

The expression should as below:

addDays('1900-01-01',add(int(items('Apply_to_each')?['Due Date']),-2))

 

Best regards,

Alice       

 

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
RobElliott
Super User
Super User

@Magnus in your SharePoint Create item action, for the relevant date column add the following expression into the expression tab in the dynamic content:

addDays('1900-01-01',sub(int(item()?['When']),2))
where When is the name of the column in the spreadsheet.
excelDateFormattoSP.png

 

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.
v-alzhan-msft
Community Support
Community Support

Hi @Magnus ,

 

Please take a try with @RobElliott 's suggestion and let me know if you have any problem.

 

Best regards,

Alice       

 

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @RobElliott.

I tried your solution but when I run the Flow I end up with this error message in the Create item action:

 

InvalidTemplate. Unable to process template language expressions in action 'Create_item' inputs at line '1' and column '2201': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

 

Any hints on what might be the problem?

Hi @Magnus ,

 

Could you please share a screenshot of the configuration of your flow?

 

Best regards,

Alice       

 

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @RobElliott ,

 

Please take a try wo create the flow as below:

1.png

 

The expression should as below:

addDays('1900-01-01',add(int(items('Apply_to_each')?['Due Date']),-2))

 

Best regards,

Alice       

 

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,432)