Function to get email 14 days prior to a dated listed in Excel

[ASmithRouse]

Hello! I am attempting to get an email from Power Automate 14 days prior to a date listed in an Excel column. I have started with Recurrence > List rows present in a table > Foreach (Value) > Condition > If yes. I believe the issue is with my condition. I have included three screenshots below of my current flow. The function that I am using in the condition is equal to  formatDateTime(addDays(utcNow(),14),'MM-dd-yy')

[the_dude]

So if i understand correctly your condition always goes into the 'No'  bin even when you'd expect it to go to 'Yes'.

Perhaps you should try doing the same format datetime on the left side aswel.

formatDateTime(EXCELCOLUMN,'MM-dd-yy')

[ASmithRouse]

That is correct, it is going to no and not yes. However, when I use the function you gave and I get an error that The expression is invalid. In insight?

[trice602]

Hi @ASmithRouse ,

Please try and change expression formatting to:

MM-dd-yyyy

[ASmithRouse]

That did not work either 😞

[trice602]

Hi @ASmithRouse ,

Please share a screenshot of your Excel table - then we can resolve quickly.

[ASmithRouse]

Please see below 

[trice602]

Hi @ASmithRouse ,

Thanks for sharing.  Please try wrapping your condition like this:

formatDateTime(dynamicContent-ClassStartDate, 'MM-dd-yyyy') -- because you need to match the date format with the addDays format expression.

In this sense, you will match dates in Excel, such as 01-10-2024, to your addDays expression (01-10-2024).

[ASmithRouse]

I get an error that, "The expression is invalid." Also just for clarity am I putting this function on the left or right side of the equals to option? ThanksQ 

[trice602]

Hi @ASmithRouse ,

This would go on the left side so we can get a date out of Excel that reads 01-10-2024 to match you addDays expression already cleaned up to match (example 01-10-2024).

If you can share your edited conditional action and the expressions, that will be helpful!

[ASmithRouse]

I am still getting the error that it is invalid. Photo below. 

[trice602]

Hi @ASmithRouse ,

Sorry about that.  It looks like you typed in the dynamic content as the text , you have to select it for the left side as dynamic content.

On the left side, start with an expression, type in

formatDateTime(

then select the dynamic content tab and select the Excel dynamic content for your date column...

then continue entering formatDateTime(yourdynamiccontent, 'MM-dd-yyyy')

[ASmithRouse]

When I do that with the expression and then select the dynamic content I only get value and body as an option not the correct column. thank you so much for your ongoing assistance. 

[trice602 · Accepted solution]

Hi @ASmithRouse ,

Jumping back in here.  I realize that when I joined the conversation, I didn't read it correctly and made a mistake.  I made a test flow and expected my flow to get the row with ID 15, shown below.  That's today's date + 14 days so we expect one row to be true (Yes side).  Please review and update your expressions accordingly, the ISO format is 'yyyy-MM-dd'.

Important highlights:  Your list rows present in a table needs to have the date format set to ISO 8601, shown below.

Here's my condition action.

The left side expression:

The right side expression:

This is going to get the row(s) that for the expression 2024-01-10 equal to 2024-01-10.  My test flow and run does return one row as expected, ID 15.

[trice602]

Hi @ASmithRouse ,

I wanted to share one more thing with you.  Often times the "format" in an excel column is 1/10/24 but the if formatted as a date, it is still 1/10/2024, see example below.

In your list rows present in a table, when you turn on the date/format ISO 8601, your dates automatically get converted so this condition also works.

[ASmithRouse]

YES! Thank you so much! That worked. I do have one additional question. Although it is working, it is coming up with an error, BUT it is working and emailing me correctly. It says (screenshot below), "Unable to process template language expressions for action 'Condition_2' at line '0' and column '0': 'In function 'formatDateTime', the value provided for date time string '' was not valid. The datetime string must match ISO 8601 format.'."

Below are screenshots of my condition. Again, thank you so much! 

[trice602]

Hi @ASmithRouse ,

 Nice!  Please mark one or more replies as a solution to help other find this discussion too!  Ok, a most likely reason this occurs is in your date column in Excel, you have a blank value or something that is not a date, please check.  You can also click on "show next failed" and see the row, then go to that row in Excel and review.

Again, make sure you have turned on date/format ISO 8601 in your List rows present in a table action.