Unanswered

Help sending mail if date is <= addDays(utcNow(),5,'yyyy-MM-dd')

(0) Share

Report

Posted on by PinkCamel

Greetings, absolute novice Power Automate here.

So I've looked at a couple of topics related to this but none have been of help. I've been trying to set a scheduled flow that runs at 8 am every day to check a table where I have historical bond purchases, their maturity date, ISIN, and quantity (how many of each bond are currently in the portfolio):

I am using the "Apply to Each" action after "List rows present in a table" to check for each row if the date in column "Maturity Date" *items('Apply_to_each')?['Maturity Date'])* is less than today +5 days *addDays(utcNow(),5,'yyyy-MM-dd')*. Also running "List rows" with DateTimeformat "Serial number", running ISO 8601 makes the flow runs endlessly:

The flow runs 'successfuly' but all expression results are false, even though the last row in the table is in fact tomorrow (6/15/23 or 45092 as you can see in the output of "list rows" on the right), so the condition should be met:

I would also need the mail to be sent only for bonds for which there is a "Quantity" greater than 0 (obviously, since bonds with a quantity of "0" are no longer in the portfolio), but I'm trying to get the date conditional sorted first. Any insights as to how to make this work are greatly appreciated!

Thanks!

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

ManishSolanki 15,091 Super User 2025 Season 2 on at

Like (1)

Report
Hi @PinkCamel

In the 'List rows present in a table' action, click on 'Show advanced options' and set 'DateTime Format' as ISO 8601 as shown below:
after Setting date time format flag, pls check your dates comparison.

For condition, quantity > 0, pls add a new condition by wrapping column value in int formula to convert it into integer:
Expression used in left side to check the value of Quantity column is:
int(item()?['Quantity'])

If this helps, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

Was this reply helpful? Yes No
David_MA 12,966 Super User 2025 Season 2 on at

Like (0)

Report
In your screen shot where it works but all the results are false, it looks like the spreadsheet is returning the serial date. You can use this expression to convert this to the ISO8601 date format and I think that will resolve your problem:

addDays('1899-12-30', int(items('Apply_to_each')?['Date']), 'yyyy-MM-dd')

Replace ['Date'] with the cell from your spreadsheet (Maturity date).

Was this reply helpful? Yes No
PinkCamel 3 on at

Like (0)

Report

Hi, thanks for replying!
After many hours I got it working. So as I mentioned, setting 'DateTime Format' as ISO 8601 did not solve it by itself. Oddly, what I think made it work was changing the table column "Maturity Date" format directly in the excel sheet to "yyyy-MM-dd" so that it matches with the expression's format. The "Quantity" expression on the other hand worked like a charm!

Was this reply helpful? Yes No