Compare date value from excel column to today date

(1) Share

Report

Posted on by uronohan

Hi,

I have an a flow to send reminder to passport holder to extend their passport base on the excel sheet with column name 'EMAIL REMINDER (180 DAYS BEFORE)' the value of the column is shown as '15-Aug-24' format. I need to compare the value with today date and the email reminder will send to user email.

it looks like the variable vReminderDate is an array of each row value, can someone help ?

Thanks in advance!

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Michael E. Gernaey 53,488 Super User 2025 Season 2 on at

Like (1)

Report

Hi,

I am going to say that what you have is not what you are explaining. And since your Condition is outside of an Apply to each , yes it will trigger ANOTHER apply to each.

1. You do not need arrays at all

2. You do not need more than 1 Apply to each

3. Creating the variable for Todays Date. Ok, not necessary, but ok

You were perfect until you added all that other stuff.

1. List rows (check)

2. Iterate Rows (check) (aka Apply to Each)

--Inside Apply to Each

2a. use Condition to check to see if you need to email them or not (TO BE ADDED) -- you have it but in the wrong place, it has to be inside and you

do not need arrays and stuff.

(Today + Add 180 Days) = Their Date and of course you only want the Date not the Times.

-Turn Today into a Date

addDays(formatDateTime(utcNow(), "yyyy-MM-dd"), 180)

is equal to

formatDateTime(theircolumndate, "yyyy-MM-dd")

2b. Send the Email (TO BE ADDED)

And bingo done.

Note, I dont see any tracking, and I definitely suggest you write to a list somewhere, that you did this and that the email was sent successful etc.

Was this reply helpful? Yes No
uronohan 15 on at

Like (0)

Report

Hi @FLMike

Is this how it should be done ? it seem like it won't let me pick the passport expire date column

my column name on excel

Could you please suggest how I should do ?

Thank you!

Was this reply helpful? Yes No
Suggested answer

Inogic 1,135 Super User 2025 Season 2 on at

Like (0)

Report
To send the reminder email to the passport holders from the Excel file please follow the below steps:-

Get today’s date in a compose or in a variable then add 180 days in it and format it with the expression formatDateTime(addDays(utcNow(), 180), 'yyyy-MM-dd')

Use apply to each action on the Excel file to get the expiry date of all records from Excel and get the Expiry date in a compose and format it with expression formatDateTime(parseDateTime(items('Apply_to_each')?['Expired Date']), 'yyyy-MM-dd')

Check both outputs of the composes in the Condition action and use the send action in the Yes part.

Hope this helps.

Thanks!
Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/

Was this reply helpful? Yes No
uronohan 15 on at

Like (0)

Report

Hi @Inogic,

Thank you for this wonderful solution.

There is another issue. The list includes the family members of the expat, and their dependents will have the same email as the expat. When a passport is set to expire in 180 days, the list of dependents should also be included in the expat’s email. This means that in one email, there should be a list of passports that are due to expire. Currently, each passport expiration triggers a separate email. As a result, if an expat has five family members whose passports expire on the same day, they will receive five emails.

Was this reply helpful? Yes No