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

Daily flow to send a predefined email if a column in an excel table contains a date older than 6 months?

Hi all!

 

I am fairly new to this awsome technology and have tried do dog into threads around dates and how to set that up but i cannot get it to work with my requirements so I am hoping you guys can help :-)....

 

I want to create and shcedule a weekly flow to send a predefined "remember to" email if a column like "profile updated" for example in an excel table contains a date older than 6 months?

 

how do i achieve this? 

 

My Excel table date columns looks as follows and are formatted as "dates" in the format 2012-03-14:

emiled_0-1597743344946.png

 

I would greatly apprecciate your help

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
manuelstgomes
Community Champion
Community Champion

Hi @emiled 

 

You can do the following:

  1. Create a schedule trigger with the schedule in mind.
  2. Fetch the values from Excel.
  3. Do a "For Each" for each row
  4. (Tricky part) The dates will come in number format. Here's how to convert them to dates https://manueltgomes.com/microsoft/powerautomate/convert-excel-number-to-date/
  5. Now that you've converted it you can compare them with a simple if. Here's how to add days in the left comparison https://manueltgomes.com/reference/powerautomate-function-reference/flow-adddays-function/
addDays(utcNow(), (30*6) ,'yyyy-MM-ddTHH:mm:ssZ'))

 I know that 30*6 is not 6 months, but there are no addMonths functions

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference

 

Can you please check if and let me know if you have any questions?

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

View solution in original post

Hi @emiled 

 

The issue is with the variable variables('Profile_x0020_Created').

 

You need, at the beginning of your Power Automate, to have a new variable created. You can create with a simple name like "Profile_Created" and using it like variables('Profile_Created')

 

The error message is indicating that you're using a variable without initializing it first.

 

Can you please check if and let me know if you have any questions?

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

View solution in original post

3 REPLIES 3
manuelstgomes
Community Champion
Community Champion

Hi @emiled 

 

You can do the following:

  1. Create a schedule trigger with the schedule in mind.
  2. Fetch the values from Excel.
  3. Do a "For Each" for each row
  4. (Tricky part) The dates will come in number format. Here's how to convert them to dates https://manueltgomes.com/microsoft/powerautomate/convert-excel-number-to-date/
  5. Now that you've converted it you can compare them with a simple if. Here's how to add days in the left comparison https://manueltgomes.com/reference/powerautomate-function-reference/flow-adddays-function/
addDays(utcNow(), (30*6) ,'yyyy-MM-ddTHH:mm:ssZ'))

 I know that 30*6 is not 6 months, but there are no addMonths functions

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference

 

Can you please check if and let me know if you have any questions?

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Hi @manuelstgomes and thank you for your answer.

 

I am unfortunately not really grasping how to incorporate the date conversion into my flow could you please give an example how that would look for my situation?

 

I am experiencing issue when trying to handle dates to update my list from a new list (export from another system) as well.

I have tried to get around this by creating the master in SharePoint list instead but that does not work either.

 

I really apprecciate the help!

 

thank you. 

 

see this for example for my update flow

 

emiled_0-1597907253391.png

emiled_1-1597907345405.png

 

I was trying to reference my input date and convert it at the same time in this flow but that did not work. 

This is the formula I tried to no avail:

formatDateTime(variables('Profile_x0020_Created'),'yyyy-MM-dd')
 
 

 

Hi @emiled 

 

The issue is with the variable variables('Profile_x0020_Created').

 

You need, at the beginning of your Power Automate, to have a new variable created. You can create with a simple name like "Profile_Created" and using it like variables('Profile_Created')

 

The error message is indicating that you're using a variable without initializing it first.

 

Can you please check if and let me know if you have any questions?

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022- Season 2 has kicked off!

Users online (3,326)