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

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (3,096)