cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SgtMar10
New Member

Tracking dates in excel and triggering an email

First time with Flow so forgive me if this is a simple fix...

 

We have an Excel document that tracks calibration dates of some of our equipment. I'd like to be able to send an email 90 days before the due date so we can arrange for calibration and rentals. Any Ideas on how to do this?

1 ACCEPTED SOLUTION

Accepted Solutions
v-bacao-msft
Community Support
Community Support

Hi @SgtMar10 ,

 

The prerequisite for implementing such a requirement is that the data is stored in an Excel table, not just in an Excel sheet.

If the above condition is met, and the type of Due Date field is Date, its value is a String value output in Flow, as below:

73.PNG

 

You could refer to the following method to configure Flow.

  • Set Flow to execute once a day and get all the rows in Excel table.
  • Iterate through all the rows and use Condition to filter out the eligible rows.
  • Since the type of Due Date field is Date, the data obtained in Flow is a String, which needs to be converted to Date using the following formula:
addDays('1899-12-31',int(item()['Due Date']),'yyyy-MM-dd')
  • Then compare the current date plus 90 days is equal to Due Date, if equal, send an email.
addDays(utcNow(),90,'yyyy-MM-dd')

Image reference:

74.PNG

 

Edit:

At the time of conversion, there may be errors using 1899-12-31 as a reference. Please configure according to actual output.

If the result obtained using 1899-12-31 is inconsistent with the actual, please change it to 1899-12-30.

 

Please take a try.

 

Best Regards,

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

View solution in original post

1 REPLY 1
v-bacao-msft
Community Support
Community Support

Hi @SgtMar10 ,

 

The prerequisite for implementing such a requirement is that the data is stored in an Excel table, not just in an Excel sheet.

If the above condition is met, and the type of Due Date field is Date, its value is a String value output in Flow, as below:

73.PNG

 

You could refer to the following method to configure Flow.

  • Set Flow to execute once a day and get all the rows in Excel table.
  • Iterate through all the rows and use Condition to filter out the eligible rows.
  • Since the type of Due Date field is Date, the data obtained in Flow is a String, which needs to be converted to Date using the following formula:
addDays('1899-12-31',int(item()['Due Date']),'yyyy-MM-dd')
  • Then compare the current date plus 90 days is equal to Due Date, if equal, send an email.
addDays(utcNow(),90,'yyyy-MM-dd')

Image reference:

74.PNG

 

Edit:

At the time of conversion, there may be errors using 1899-12-31 as a reference. Please configure according to actual output.

If the result obtained using 1899-12-31 is inconsistent with the actual, please change it to 1899-12-30.

 

Please take a try.

 

Best Regards,

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

View solution in original post

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

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Users online (19,990)