cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AVG
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

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
PrasadAthalye
Super User
Super User

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

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community
abm
Super User
Super User

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 blogPower Automate Video Tutorials

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? 

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





AVG
Regular Visitor

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

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





AVG
Regular Visitor

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

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

AVG
Regular Visitor

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! 

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
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!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,390)