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

Converting Integer to date - empty filed

Hi All,

 

I am using a solution offered here:

 

https://powerusers.microsoft.com/t5/Building-Flows/Excel-dates-turned-into-integers/td-p/130364

 

And in this case this function:

 

if(empty(items('ApplyEach_Dates')?['YourDateField']),'',addDays('1899-12-30',int(items('ApplyEach_Dates')?['YourDateField']),'MM/dd/yyyy'))

 

does exactly what I am aiming for:

 

Convert excel online number value to date.

 

However in some cases the cells which need to be converted are empty - and this returns an error and the flow fails.

 

How can I adjust the above formula to return just "-" in case the filed that its aiming to convert is just empty?

 

Many thanks in advance!

Best regards,
Ivan

1 ACCEPTED SOLUTION

Accepted Solutions

Made it work with changing the fields values w/o date to null 

 

if(equals(items('Apply_to_each_2')?['Deadline 3'], 'NULL'), '', addDays('1899-12-30', int(items('Apply_to_each_2')?['Deadline 3']), 'MM/dd/yyyy'))
 
 

View solution in original post

3 REPLIES 3
ScottShearer
Super User III
Super User III

@IPGeorgiev 

Take a look at the coalesce(), isBlank() and isEmpty() expressions - one or all of them will probably work for you..  Documentation can be found here.

The colaesce() expression allows you to provide a default value in the case of a null value.

Please let me know if you need more assistance.

 

 

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

Hi @ScottShearer ,

 

I have tried with the suggestion but cant make them work 😞

 

if(IsBlank(items('Apply_to_each_2')?['Deadline 4']), '', addDays('1899-12-30', int(items('Apply_to_each_2')?['Deadline 4']), 'MM/dd/yyyy'))

 

This is one of the attempts, so this is under apply to each, so the same is applied for every row and in case the field is blank it should just return "" and proceed, however it returns and error and doesnt work.

 

colaesce works as long as the cell is not empty, otherwise the flow is successful, but stops at one point and doesnt execute the next steps.

 

Thanks in advance!

Best regards,

Ivan

Made it work with changing the fields values w/o date to null 

 

if(equals(items('Apply_to_each_2')?['Deadline 3'], 'NULL'), '', addDays('1899-12-30', int(items('Apply_to_each_2')?['Deadline 3']), 'MM/dd/yyyy'))
 
 

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.

Top Solution Authors
Users online (966)