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
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (5,515)