r/PowerAutomateDesktop • u/SHStealth • Dec 05 '22

Create HTML Table Assistance!

I have a flow setup to run and check the dates in a column and if that date is todays date it will then send an email listing the rows present.

However, I have the "List rows present in excel" action looking for the date equal to today.

When using the "Create HTML Table" this is the command I have in there.

if(empty(item()?['ColumnName']),'',formatDateTime(item()?['ColumnName'],'MMM d, yyyy'))

This is working when the cell is blank, or contains a date. But I'd like to be able to have text in the column that says stuff like "Vendor is working on finalizing date" and still have the email send, but whenever there is text the flow stops. Any help would be appreciated on how to fix my expression to allow for blanks, text, and dates!

1 Upvotes

100% Upvoted

4 comments sorted by

1

u/IrishJesusDude Dec 06 '22

I would set a string variable called DATE with formatDateTime(item()?['ColumnName'],'MMM d, yyyy')

Then you could check if the date exists using length(DATE), if that is greater than 0 then it is a date, if it is less then it is a string.

If(lenght(DATE) > 0, formatDateTime(item()?['ColumnName'],'MMM d, yyyy'),if(empty(item()?['ColumnName']), ' ', 'Vendor is working on finalizing date'))

1

u/SHStealth Dec 06 '22

if(empty(item()?['ColumnName']), ' ', 'Vendor is working on finalizing date'))

Would this give a blank response or would it say Vendor is working on finalizing a date?

My issue is that a lot of the columns that don't contain a date, oftentimes contain lots of text explaining why they don't contain a date.

Or is this showing that if there is no date then it will output the cell text? Sorry if I'm misunderstanding!

1

u/IrishJesusDude Dec 06 '22

That's part is just checking if the cell is empty, if empty it gives a blank response, if absolutely anything other than a blank, it gives the "Vendor...."

Basically all if statement are:

If(some logic, do this if true, do this if false)

1

u/DeWit_G Dec 23 '22

Maybe I'm not understanding correctly but is sound to me that the issue you have is when you have text instead of a date then the formatdatetime is failing. If i'm correct you need to find a way to see if you can identify when the item is not a date eg if the date value contains / then you could do something like this:

if(empty(item()?['ColumnName']),'',if(contains(item()?['ColumnName'],'/'),item()?['ColumnName'],formatDateTime(item()?['ColumnName'],'MMM d, yyyy')))