Flow to send email depending on contents of two excel cells

(0) Share

Report

Posted on by JCS_1665

Hi folks

I've been able to create basic flows before but would greatly appreciate some help with my latest one.

I have the following two flows;

FLOW 1 - this is all set up and running fine, so is just here for context

Trigger - completion of an MS form

Some info from the form is added to an excel table. Two columns in the table relate to future dates; Date 1 holds a date when the email needs to be sent and Date 2 holds a date to confirm the email has been sent. Currently Date 2 is empty by default.

FLOW 2

Trigger - scheduled daily recurrence

Current time

Convert current time to UTC+00:00 Dublin, Edinburgh, Lisbon, London

Check excel table; if Date 1 = today's date AND Date 2 is empty [HELP NEEDED ON THIS PART AS THIS IS WHERE IS FAILING]

- send an email (I'm OK with this part)

- update Date 2 cell with today's date (HELP NEEDED ON THIS PART)

Here's a screenshot of flow 2. It currently includes Initialize Variable and Set Variable as I was trying to set up and use a boolean value re Date 2 in the condition;

Here's hoping you can help.

Thanks

Jan

I have the same question (0)

All responses (8)

Answers (1)

v-liwei-msft on at

Like (0)

Report

Re: Flow to send email depending on contents of two excel cells

Hi @JCS1665 ,

When you use the “List rows present in a Table” action in Power Automate to get data from Excel that contains dates, you might be surprised to see that the data in the date column is actually an integer and not a date.

This is because Excel stores dates as an integer. The integer represents the number of days that have elapsed since the 1st January, 1900.

You can use the addDays function to add the integer returned by Excel to '1899-12-30', then use the convertTimeZone function to convert it to your time zone time: convertTimeZone(addDays('1899-12-30',int(item()['Date1'])),'sourceTimeZone','destinationTimeZone')

Conditions of use:
1.greater than or equal to today's start time and less than or equal to the end time to determine whether the time of Date1 is within the whole day of today.
2. Check if Date2 is empty.
3.If the condition is met, use "update a row" to update Date2.

I think this link will help you a lot: https://www.tachytelic.net/2020/11/convert-excel-dates-power-automate/

Best Regards,
Levi

Was this reply helpful? Yes No
JCS_1665 29 on at

Like (0)

Report

Re: Flow to send email depending on contents of two excel cells

Thanks so much for your help @v-liwei-msft. I'll take a look at this next week and report back. Have a great weekend.

Was this reply helpful? Yes No
JCS_1665 29 on at

Like (0)

Report

Re: Flow to send email depending on contents of two excel cells

Hi @v-liwei-msft, I've tried applying your suggestion but have fallen at the first hurdle. The expression in the first part of my Condition is this:

But when I try and select the StartOfDate expression for the third "Choose a Value" column, I get a message saying "This expression is invalid".

What am I doing wrong?
Thanks
Jan

Was this reply helpful? Yes No
v-liwei-msft on at

Like (0)

Report

Re: Flow to send email depending on contents of two excel cells

Hi @JCS1665 ,

Change this to UTC instead of UtcNow :

Best Regards,
Levi

Was this reply helpful? Yes No
JCS_1665 29 on at

Like (0)

Report

Re: Flow to send email depending on contents of two excel cells

Hi @v-liwei-msft
I'm confused; isn't the part you've suggested I change the Source Time Zone, e.g., isn't that normally UTC for MS products?
Thanks
Jan

Was this reply helpful? Yes No
JCS_1665 29 on at

Like (0)

Report

Re: Flow to send email depending on contents of two excel cells

Hi @v-liwei-msft - I did try your suggestion by the way but got the same result as before.

Regards
Jan

Was this reply helpful? Yes No
Verified answer

JCS_1665 29 on at

Like (0)

Report

Re: Flow to send email depending on contents of two excel cells

Hi @v-liwei-msft - due to time constraints I've had to go with a simpler solution of adding an outlook event instead of sending the email. Thanks again for your help though, much appreciated.

Was this reply helpful? Yes No
v-liwei-msft on at

Like (0)

Report

Re: Flow to send email depending on contents of two excel cells

Hi @JCS1665 ,

Glad to see you solved the problem.
You could mark your answer/solution as a 'Solution' to close the case.

Best Regards,
Levi

Was this reply helpful? Yes No