cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Skipping empty fields in an expression using INT

Hi,

 

I am importing an Excel spreadsheet into a SharePoint list.  Once of the columns in Excel is a date but in Excel this is represented as ticks rather than traditional date/time format.  To combat that I am converting the ticks to date format using the following expression

 

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

 

flow-int-1.jpg

 

This works great so long as the column in Excel has data in it.  However if the column is empty the INT function throws an error

 

flow-int-2.jpg

 

Is there a way I can wrap the expression I am using to ignore the row if the date field is empty?  I've looked at using conditions but this just products lots of duplicate entries in the SharePoint list.

 

Thanks

Rob

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Hi @Rob_CTL ,

 

The same requirement here and you could also take a try with the expression below:

https://powerusers.microsoft.com/t5/Building-Flows/Filter-Out-Blank-Excel-Cells-when-Updating-ShareP...

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

1.png

 

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

6 REPLIES 6
Highlighted
Helper I
Helper I

you need to check if the date is null

 

if(equals(items('Apply_to_each')?['Date'],''),'1900-01-01',addDays('1899-12-30', int(items('Apply_to_each')?['Date']), 'yyyy-MM-dd'))

 

hopefully that will work

Highlighted

Thanks for the reply @mmorley   Unfortunately that creates new items and populates the Date column in SharePoint with a date of 1900-01-01 whenever the date column is empty on the Excel spreadsheet. 

 

Rob

Highlighted

I think I've got a solution, I've added a filter array that filters out the rows with NULL in the date column before it gets injected into SharePoint.  Seems to work from the initial testing, I need to try it with lots of rows but so far so good.

 

flow-int-3.jpg

 

Rob

Highlighted

my solution was to get around the error you were having of converting null entries to int as you were still putting these entries in, you can change the '1900-01-01' to '' to insert the records including ones with empty dates

 

but the other solution as you have found is to perform a filter on the results

 

remember if you have a lot of entries to enable pagination and set maximum count to retrieve

 

Highlighted

@mmorley thanks for the help

Highlighted
Community Support
Community Support

Hi @Rob_CTL ,

 

The same requirement here and you could also take a try with the expression below:

https://powerusers.microsoft.com/t5/Building-Flows/Filter-Out-Blank-Excel-Cells-when-Updating-ShareP...

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

1.png

 

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

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