Skip blank cells in Date Column

[harshjoshiD]

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.

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 🙂

[v-bofeng-msft · Accepted solution]

Hi @harshjoshiD ,

Please try:

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

Best Regards,

Bof

[harshjoshiD]

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