cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Issue getting dates from excel

Hi,

 

There are loads of posts with similar issues to mine, but none of the solutions quite seem to work. I'm hoping that if I outline my specific scenario then someone might be able to help me!

 

I am mid way through testing a new SharePoint site that tracks all of my firms chargeable work through the 20-30 stages of each assignment. We're transferring to SharePoint from an excel spreadsheet that has the same purpose. 

 

Transferring the 1,000 lines of data over to SharePoint with copying and pasting in to quick edit view was fine, but when we do the full launch we'll have almost 10 times that, so I want to try and use a flow. Having a button flow set up for this will also help us when it comes to rolling forward the assignments for next year.

 

I'm having trouble with date fields in the flow. Almost all of the columns in my spreadsheet are date fields and need to be pasted to the equivalent date field in SharePoint. There are lots of suggestions out there to use the addDate function to convert the date from an integer to a date. I'm not sure this will work if the stages that haven't been completed yet need to be empty?

 

Does anybody have any suggestions?

 

Thanks,

A

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Issue getting dates from excel

Hi @AVG 

 

Sorry, my mistake. Forgot about the apply to each loop.

 

Try:

 

if(empty(items('Apply_to_each')?['Period End']),null,addDays('1899-12-30',int(items('Apply_to_each')?['Period End']),'yyyy-MM-dd'))

 

 





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

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




View solution in original post

10 REPLIES 10
Highlighted
Responsive Resident
Responsive Resident

Re: Issue getting dates from excel

May be you can use Empty function to check if the column is null and if not, use formatdatetime() to format it?

Highlighted
Super User III
Super User III

Re: Issue getting dates from excel

Hi @AVG 

 

Please see below steps how to check an excel sheet column is blank or not.

 

image.png



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 blog
Highlighted
Regular Visitor

Re: Issue getting dates from excel

Hi @PrasadAthalye 

 

Thanks for the suggestion 🙂

 

I've tried putting the below in to a compose function, but get an error message to say the expression is invalid.

 

if(empty(['Period End']),null,addDays('1899-12-30',int(['Period End']),'yyyy-MM-dd'))

 

Any idea where I'm going wrong? 

Highlighted
Super User III
Super User III

Re: Issue getting dates from excel

Hi @AVG 

 

In your expression you want to click your dynamic content inside the empty() expression

if(empty(<Dynamic content here>?['Period End']),null,addDays('1899-12-30',int(<Dynamic content here>?['Period End']),'yyyy-MM-dd'))

Replace <Dynamic content here> with your dynamic content Excel Value

 

 

Please post a screenshot if the above does not work





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

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




Highlighted
Regular Visitor

Re: Issue getting dates from excel

Hi @Jcook 

 

I can't get this to work I'm afraid!

 

When I don't have anything in my expression, I have a full list of dynamic content available:

 

Capture 1.JPG

 

However, as soon as I out an expression in and then click back to the dynamic content tab, I'm left with only these options. I've used the 'list items present in table' step to get my assignments from excel. Is that correct?

 

Capture 2.JPG

Highlighted
Super User III
Super User III

Re: Issue getting dates from excel

Hello @AVG 

 

You want to click your excel 'value' than put a ?['Period End']

 

You can use this expression, try copying this:

if(empty(body('List_rows_present_in_a_table')?['value']?['Period End']),null,addDays('1899-12-30',int(body('List_rows_present_in_a_table')?['value']?['Period End']),'yyyy-MM-dd'))

 





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

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




Highlighted
Regular Visitor

Re: Issue getting dates from excel

Hi @Jcook 

 

We're getting somewhere! Still not working, but the error has changed at least. I now get this when I run a test:

 

Unable to process template language expressions in action 'Create_item' inputs at line '1' and column '18400': 'The template language expression 'if(empty(body('List_rows_present_in_a_table')?['value']?['Period End']),null,addDays('1899-12-30',int(body('List_rows_present_in_a_table')?['value']?['Period End']),'yyyy-MM-dd'))' cannot be evaluated because property 'Period End' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

Highlighted
Super User III
Super User III

Re: Issue getting dates from excel

Hi @AVG 

 

Sorry, my mistake. Forgot about the apply to each loop.

 

Try:

 

if(empty(items('Apply_to_each')?['Period End']),null,addDays('1899-12-30',int(items('Apply_to_each')?['Period End']),'yyyy-MM-dd'))

 

 





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

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




View solution in original post

Highlighted
Regular Visitor

Re: Issue getting dates from excel

This is perfect @Jcook - my error has moved along a column, so just need to complete this for the other 30 columns and I'll be sorted!

 

Just so that there's a complete solution out there for anyone else reading this, I had to put apostrophes at each end of the apply_to_each, so my final expression ended up being this:

 

if(empty(items('Apply_to_each')?['Period End']),null,addDays('1899-12-30',int(items('Apply_to_each')?['Period End']),'yyyy-MM-dd'))

 

Thanks again for your help - you've saved me hours of copying and pasting! 

Highlighted
Super User III
Super User III

Re: Issue getting dates from excel

Thanks @AVG  I always forget the single quotes for that expression when free handing it haha. I have modified my reply so users would not be confused.

 

Glad to see your issue fixed 🙂





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

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




Helpful resources

Announcements
FirstImage

Microsoft Ignite 2020

Check out the announcement of Power Platform content at Microsoft Ignite!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (9,241)