Power Platform Community Forum Thread Details

Hello,

I am trigging to build a flow where the trigger is a reoccurrence every day at 10 AM. I then want my flow to look within my excel table, find all days within the present date through two weeks back, of those rows find only the names that have a "No" written in the "Notified" column, create a list of the names and corresponding dates and send them out in an email.

My flow says it is working but within the email that is sent every day at 10 AM, names and dates are not listed. I am assuming it might be because Excel stores dates as whole numbers. Though is formatted to be short dates in my columns, that's the only issue I can think of. When using Compose to see the the Outputs I am seeing everything listed of what should be true, but PA is still saying it does not meet my conditions. How do I fix this?

Attached is the compose outputs, image of my flow (not including the initial trigger), and the email that is being sent out that has nothing listed.

The current flow is as followed:

Reoccurrence (1 Interval, Day Frequency, Time Zone UTC Eastern Time, Start Time 10:00 AM)

>List Rows Present in a Table (Location and Connections are correct)

>Initialize Variable (String "TempEmailBody")

>Initialize Variable (String "EmailBody")

>Initialize Variable (String "DebugInfo")

>Apply to Each (Output=List Rows Present in a Table)

>Compose (Inputs: Notified column from Excel Table, Date of ASA from Excel Table, Format Date Time:

formatDateTime(addDays(utcNow(), -14), 'yyyy-MM-dd'), Format Date Time:formatDateTime(utcNow(), 'yyyy-MM-dd'))
> Append to String Variable (Name: DeBugInfo, Value:Notifed colomn from Excel Table, Date of ASA from Excel Table, Format Date Time:
formatDateTime(addDays(utcNow(), -14), 'yyyy-MM-dd'), Format Date Time:formatDateTime(utcNow(), 'yyyy-MM-dd'))
>Condition (AND function: If the "Notified"=No, If "if(empty(items('Apply_to_each')?['Date_of_ASA']), '1900-01-01', formatDateTime(addDays('1900-01-01', div(sub(ticks(items('Apply_to_each')?['Date_of_ASA']), ticks('1900-01-01')), 864000000000)), 'yyyy-MM-dd'))" is greater or equal to "formatDateTime(addDays('1900-01-01', div(sub(ticks(utcNow()), ticks('1900-01-01')), 864000000000)), 'yyyy-MM-dd')", IF 

if(empty(items('Apply_to_each')?['Date_of_ASA']), '1900-01-01', formatDateTime(addDays('1900-01-01', div(sub(ticks(items('Apply_to_each')?['Date_of_ASA']), ticks('1900-01-01')), 864000000000)), 'yyyy-MM-dd')) is less than or equal to formatDateTime(utcNow(), 'yyyy-MM-dd')"
>TRUE=>Compose (concat(item()?['Client''s Name'], ' - ', formatDateTime(addDays('1899-12-30', int(item()?['Date of ASA'])), 'MM/dd/yyyy'), '<br/>'))>Append to String Variable (Name:TempEmailBody Value:Outputs)
>False=>Append to String Variable (Name:DeBugInfo Value:

concat('Condition False -> ', item()?['Notified '], ', ', item()?['Date of ASA'], ', Start Date Limit: ', formatDateTime(addDays(utcNow(), -14), 'yyyy-MM-dd'), ', Today: ', formatDateTime(utcNow(), 'yyyy-MM-dd'))
>Outside of Apply to Each>Compose(DebugInfo)
>Append to String Variable (Name: EmailBody Value:TempEmailBody)
>Send an Email (V2) (Source: EmailBody)
 

Categories:

Building flows

@HH-31031439-0,

What is the format of date in the excel file?

Convert Excel's date to a usable format in Power Automate

formatDateTime(addDays('yyyy-mm-dd', int(item()?['Date of ASA'])), 'yyyy-MM-dd')

I coudln't able to understand the date time format because you have multiple conversion let me simplify

AND(
equals(item()?['Notified'], 'No'),
greaterOrEquals(
formatDateTime(addDays('yyyy-mm-dd', int(item()?['Date of ASA'])), 'yyyy-MM-dd'),
formatDateTime(addDays(utcNow(), -14), 'yyyy-MM-dd')
),
lessOrEquals(
formatDateTime(addDays('1yyyy-mm-dd', int(item()?['Date of ASA'])), 'yyyy-MM-dd'),
formatDateTime(utcNow(), 'yyyy-MM-dd')
)
)

If condition is failing, add this inside the "False" branch of the Condition:

concat(
'Condition False -> ',
'Notified: ', item()?['Notified'],
', Date of ASA: ', formatDateTime(addDays('yyyy-mm-dd', int(item()?['Date of ASA'])), 'yyyy-MM-dd'),
', Start Date Limit: ', formatDateTime(addDays(utcNow(), -14), 'yyyy-MM-dd'),
', Today: ', formatDateTime(utcNow(), 'yyyy-MM-dd')
)

Ensure Correct Email Formatting

concat(
item()?['Client''s Name'], ' - ',
formatDateTime(addDays('yyyy-mm-dd', int(item()?['Date of ASA'])), 'MM/dd/yyyy'),
'<br/>'
)

kindly check and revert back.