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

Need help with a flow to evaluate an Excel column (Expiration Dates) and send an email if a date matches today's date.

I am working on a flow as stated above. I've tried both Power Automate and Power Automate Desktop. The issue is somewhere in the extraction of the dates and then the comparison to today's date.

 

Here is a portion of the table:

fcasta308_1-1602982681404.png

Here is where the flow breaks:

fcasta308_2-1602982784449.png

I could use some help understanding how the data is being stored at this point... would there be any date conversion needed? I get this error when running the flow:

fcasta308_3-1602983005788.png

Any help, guidance and/or knowledge is appreciated! Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

Appreciate the response. Scripting is a great option but I continued to research other options.

 

I found a great article that details this exact flow, it's a great read for anyone tracking expiration dates on an Excel sheet:

 

https://powerusers.microsoft.com/t5/Using-Flows/Trying-to-get-a-flow-to-send-an-email-if-the-date-in...

 

The next step is to enhance the Condition card to check for dates 90, 60 and 30 days in advance. Once that's done this is a solution for automatic reminders. 🙂

View solution in original post

4 REPLIES 4
fcasta308
Frequent Visitor

I am one step further but still banging my head on this one.

 

Excel stores date values as serial numbers, so I've been trying to design the flow so that I can pull the serial values of the my "Expiry Date" column. I've tried making all the serial values into integers; I've tried doing the conversion method discussed here: https://www.shanebart.com/ms-flow-excel-dates/ , except I do not have/need an HTML table; it appears that I am running into some kind of array type issue within my table. Here is the updated flow:

fcasta308_0-1603001571209.png

The using a "Compose" tile I applied this function:

 

if(empty(item()?['Expiry Date']),null,addDays('1899-12-30',int(item()?['Expiry Date']),'yyyy-MM-dd'))
 
While the Flow runs without errors, the output does not yield the formatted serial numbers for me to compare within a Condition.
 

 

See here - https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Select-overdue-items-from-Excel-worksheet/i...

I think it closely matches your need - though you may have to fine-tune the script. It's more straightforward compared to comparing dates outside of Excel.

 

Check out Office Scripts docs site for more usage and instructions to turn on if it is not already on. 

https://docs.microsoft.com/en-us/office/dev/scripts/develop/power-automate-integration

Appreciate the response. Scripting is a great option but I continued to research other options.

 

I found a great article that details this exact flow, it's a great read for anyone tracking expiration dates on an Excel sheet:

 

https://powerusers.microsoft.com/t5/Using-Flows/Trying-to-get-a-flow-to-send-an-email-if-the-date-in...

 

The next step is to enhance the Condition card to check for dates 90, 60 and 30 days in advance. Once that's done this is a solution for automatic reminders. 🙂

View solution in original post

Hi @fcasta308  ,

 

Have your issue been solved?

If yes, please go ahead and mark the post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify!

 

Best Regards,

Alice

 

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Kudoed Authors
Users online (2,365)