cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
harshjoshiD
Frequent Visitor

Skip blank cells in Date Column

Hello all,

 

I am new to Power Automate. I have created a basic flow which fetches the data from Sharepoint Excel file --> Formats the data in HTML Table --> Sends Email. There is a column named 'Latest Hit Date' for which I have used below expression:

formatDateTime(item()?['Latest Hit Date'],'MM-dd-yyyy')

 

Now when any cell in this column is empty/blank/null then the flow fails. It shows the error message:

failed: 'In function 'formatDateTime', the value provided for date time string '' was not valid. The datetime string must match ISO 8601 format.'.

 

I have set the DateTime in ISO 8601 format.

 

harshjoshiD_1-1664510154311.png

 

harshjoshiD_2-1664510207821.png

 

harshjoshiD_0-1664510106043.png

 

I have tested this flow multiple times, when the cells have a Date in them, the flow runs perfectly, but when you delete the Date from the cell then the flow fails. I guess Power Automate is trying to format 'null' using the formatDateTime expression which I have used.

I need to find the workaround of this. Is there any way we can ignore the empty/blank/null cells in this column..? It would be really helpful to get some help on this.

 

Thanks 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @harshjoshiD ,

 

Please try:

if(
  empty(item()?['Latest Hit Date']),
  '',
  formatDateTime(item()?['Latest Hit Date'],'yyyy-MM-dd')
)

 

Best Regards,

Bof

View solution in original post

2 REPLIES 2
v-bofeng-msft
Community Support
Community Support

Hi @harshjoshiD ,

 

Please try:

if(
  empty(item()?['Latest Hit Date']),
  '',
  formatDateTime(item()?['Latest Hit Date'],'yyyy-MM-dd')
)

 

Best Regards,

Bof

harshjoshiD
Frequent Visitor

Worked like a charm..!! Thanks for your help 🙂

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (5,477)