Power Platform Community Forum Thread Details

Hello,

I am building a Power Automate flow that reads booking data from an Excel table stored in OneDrive and sends reminder emails based on the Arrival Date.

(Build an automated workflow that reads live booking data from an Excel file that is continuously updated and sends reminder emails 1 month prior to the Arrival Date for bookings marked as Provisional.
The Excel file serves as the single live source of truth -

Checks if the same Event ID already exists in the “Sent Email Report” file.
- If it already exists, the flow does nothing (to avoid duplicate emails).
If the Event ID has not been used before, the flow checks the Contact Email field.
- If Contact Email is empty, the flow writes the event details into the “Missing Email Report” file and does not send an email.
If Contact Email exists, the flow sends an automatic reminder email to that address.
After sending the email successfully, the flow records the event details (Event ID, email address, date sent, etc.) into the “Sent Email Report” file for tracking and duplication prevention.
If there is an error during email sending, the flow records the failure and sends an error notification to the internal administrators

However, I am experiencing two major issues:

All rows are being emailed, even though I have conditions in place.
I am unsure whether the issue is related to how Excel dates are being read.

I have attached three sample Excel files for reference.

What I am trying to achieve

Trigger: Recurrence (daily at 09:00 GMT Standard Time)

Source: Excel table with the following columns:

Event Title

Event ID

Arrival Date

Contact Full Name

Contact Email

Status

Business logic:

An email should only be sent if:

Status = "Provisional"

Arrival Date is in the future

Arrival Date is exactly one month from today

Contact Email is not empty

The email has not already been sent (duplicate prevention)

The problem

Instead of filtering properly, all rows from the Excel table are being processed and emails are being sent for every row, even those that clearly do not meet the conditions.

I suspect one of the following may be happening:

The Arrival Date format is not being interpreted correctly.

The Condition expression is evaluating incorrectly.

The Apply to each loop is running on the wrong dataset.

The date comparison logic is flawed.

Excel Date Format Question

I am unsure how Excel dates should be formatted so that Power Automate reads them correctly.

In the List rows present in a table action, there is a DateTime Format option:

Serial Number

ISO 8601

Questions:

What is the recommended way to store dates in Excel for Power Automate?
- True Excel date (serial number)?
- ISO 8601 string (yyyy-MM-dd)?

Should I convert the Arrival Date column to ISO format in Excel?

What is best practice for comparing Excel dates with utcNow() and addMonths()?

Current Flow Logic (simplified)

Recurrence

→ List rows present in a table

→ Apply to each (body/value)

→ Condition:

and(

equals(items('Apply_to_each')?['Status'], 'Provisional'),

equals(

formatDateTime(items('Apply_to_each')?['Arrival Date'], 'yyyy-MM-dd'),

formatDateTime(addMonths(utcNow(), variables('OffsetMonths')), 'yyyy-MM-dd')

),

equals(empty(items('Apply_to_each')?['Contact Email']), false)

)

Despite this condition, all rows are being emailed.

Additional Information

I am also checking against a “Sent Email Report” table to prevent duplicates.

Three sample files are attached.

I can provide raw Outputs JSON from the List rows action if needed. [Removed by moderator, too long and not needed]
Any guidance would be greatly appreciated.

Categories:

Connector development

Process mining

Power Automate Desktop

Using flows

Using connectors

General topics

Your question was quite long and included a lot of information, which made it very difficult to follow. Would you mind simplifying it so it’s easier for people to help?

For the time being, the following tips may be of benefit to you:

(1) Add a Filter Query to the List rows present in a table action:

(2) Your expression needs to be in a filter array action, which I have modified to the following:

@and(
     not(equals(trim(item()?['Contact Email']),'')),
     equals(
        formatDateTime(item()?['Arrival Date'],'yyyy-MM-dd'),
        addToTime(utcNow(), variables('OffsetMonths'), 'Month', 'yyyy-MM-dd')
     )
)