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

@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
Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

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