cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Send email according to 2 different condition on excel value

Hello,

I have an excel file it contains DATE value (Deadline column in excel) and another column named Status

I want to send an email if "STATUS" value is not equals to "OK" and "Deadline" date value is less than current time (utcnow)

 

 

 

My excel file looks like this:

 

 excel.png

Deadline:13.02.2018

 

flow.png

 

My Flow:

 

1. Recurrence every 3 day

2. List rows present in a table (excel in onedrive)

3. Condition for convert date format 

@equals(formatDateTime(items('Apply_to_each')?['Deadline'], 'dd/MM/yyyy'), formatDateTime(utcNow(), 'dd/MM/yyyy'))

 

I added this to fix following error but it doesnt work

InvalidTemplate. Unable to process template language expressions for action 'Condition' at line '1' and column '2482': 'In function 'formatDateTime', the value provided for date time string '43144' was not valid. The datetime string must match ISO 8601 format.'.
4.. Another Condition to check excel if Status value is not "OK" 
@not(equals(items('Apply_to_each')?['Status'], 'OK'))

then 

5. Send email

 

What am i doing wrong? Any ideas?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Eylem,

 

If 'Deadline' date is less than today then send an email, 

The expression in Condition 2 should as below:

@less(int(items('Apply_to_each')?['Deadline']), add(int(div(sub(ticks(formatDateTime(utcnow(), 'yyyy-MM-dd')), ticks('1900-01-01T00:00:00Z')), 86400e+7)), 2))

Best regards,

Alice

View solution in original post

8 REPLIES 8
Frequent Visitor

I am still looking for a solution! Any different point of view is appreciated..

Thanks

Community Support
Community Support

Hi @Eylem,

 

 

Please take a try with expression below:

@equals(ticks(concat(formatDateTime(items('Apply_to_each')?['Deadline'], 'yyyy-MM-dd'), 'T00:00:00Z')), ticks(concat(formatDateTime(utcnow(), 'yyyy-MM-dd'), 'T00:00:00Z')))

 

You could refer to link below to see the similar scenes:

https://powerusers.microsoft.com/t5/Building-Flows/Compare-dates/m-p/54802

 

Best regards,

Alice

Hello Alice,

I've tried both of fx you told me but its still not working. Result was still same ISO date error.

Also check the link you gave me, at the end of topic another user tried the same formula as i did and he received the same ISO error as he says.

Something wrong about this

 

Any other ideas?

 

 

 

 

Hi @Eylem,

 

Please take a try to with expression below:

@equals(int(items('Apply_to_each')?['Deadline']), add(int(div(sub(ticks(formatDateTime(utcnow(), 'yyyy-MM-dd')), ticks('1900-01-01T00:00:00Z')), 86400e+7)), 2))

I have made a test on my side and have create a flow as below:

Capture.PNG

The flow would run successfully as below:

Capture.PNG

 

Best regards,

Alice

Hi @v-yuazh-msft thanks for your answer

 

I made my flow exactly as you described and my next step should be; if 'Deadline' date is less than today then send an email.

I tried to do that at Condition 2 but it failed

 

My flow:

anotherfail.png

Condition 2 same occured:

anotherfail2.png

 

 

 

Hi @Eylem,

 

If 'Deadline' date is less than today then send an email, 

The expression in Condition 2 should as below:

@less(int(items('Apply_to_each')?['Deadline']), add(int(div(sub(ticks(formatDateTime(utcnow(), 'yyyy-MM-dd')), ticks('1900-01-01T00:00:00Z')), 86400e+7)), 2))

Best regards,

Alice

View solution in original post

@v-yuazh-msft thank you, now it works like a charm 

 

I have one more question; when it sends the e-mail; "Deadline" date value showing up as "43333" in the mail body. Which is raw date format.

I want to convert it to dd/MM/yyyy

 

How can i do it ?

 

 

 

 

Hello, @v-yuazh-msft I've the same problem.

I've a question, my Excel and Flow are in Italian.

How can I change string if I want send Email if Deadline < 15 day to now ?

 

Italian format Date in Excel for Deadline is : gg.mm.aa

 

Thanks

Helpful resources

Announcements
PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (6,939)