cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DynamicsHS
Resolver I
Resolver I

How to pull the start and end date of a list of dates in the format: (Start date - End Date)?

Hi, 

 

I want to create a recurring flow that sends at the start of every Monday that sends an email to myself of the employee's who are off the this specific week. Sunday - Saturday

 

In the data I have - I do not have a start and end date it just shows a list of dates that have been approved e.g.

DynamicsHS_0-1660866383002.png

In the email I want the dates to show as:

Employee A: 15/08/2021 - 19/08/2021

Employee B: 16/08/2021 - 17/08/2021

 

Is it possible to capture the dates like this without including all the dates in-between? 

 

I do not want it do show like a string where it says:

[Employee A - 15/08, 16/08, 17/08, etc]

 

Thank you, 

Henry

1 ACCEPTED SOLUTION

Accepted Solutions
MarconettiMarco
Super User
Super User

Hello @DynamicsHS ,

please follow these steps:

1. Initialize a String variable, that will be useful to list the Employees in the email body:

MarconettiMarco_0-1660892835159.png

 

2. Initialize a temporary Array variable, to be used to list the dates as numbers and find min and max values:

MarconettiMarco_1-1660892880215.png

 

3. Initialize an Integer variable, to store the min value:

MarconettiMarco_2-1660892917901.png

4. Initialize an Integer variable to store the max value:

MarconettiMarco_3-1660892954932.png

5. Add the Excel "List rows present in a table" in order to grab the Employees rows:

MarconettiMarco_4-1660892992135.png

6. Add a "Select" action where we want to extract the Employee from the previous array:

MarconettiMarco_5-1660893014204.png

7. Add a "Compose" action in which put the following expression, useful to get only unique values from the Select outputs action:

union(body('Select'),body('Select'))

MarconettiMarco_6-1660893097499.png

8. Add an "Apply to each" control where add the following actions. The "Select an output from previous steps" field must be filled with the previous Compose outputs action:

MarconettiMarco_7-1660893128688.png

In the "Apply to each" settings it's needed to enable the "Concurrency control" and set it to 1.

MarconettiMarco_19-1660894044894.png

 

MarconettiMarco_18-1660894018975.png

 

9. Add a "Filter array" action where we want to filter the excel rows based on the employee name:

MarconettiMarco_8-1660893210680.png

 

10. Add a "Parse JSON" action, in the "Content" field put the Filter array Body. In regards of the Schema section, please first run the flow, copy the Filter array outputs, then click on the "Generate from sample" button and paste the code inside.

MarconettiMarco_9-1660893235488.png

11. Add an Apply to each loop, putting the Parse JSON Body. Inside the Apply to each control, add the "Append to array variable", in the Value add the following expression in order to convert the string date in an integer:

int(items('Apply_to_each_2')['Date'])

MarconettiMarco_10-1660893381006.png

12. Add a "Set variable" action where select the varMin variable and add the following expression:

min(variables('varNumbers'))

MarconettiMarco_11-1660893494713.png

13. Add a "Compose" action putting the following expression, useful to identify the min date from an integer:

addDays('1900-01-01',variables('varMin'),'yyyy-MM-dd')

MarconettiMarco_12-1660893553758.png

14. Add a "Set variable" action where select the varMax variable and add the following expression:

max(variables('varNumbers'))

MarconettiMarco_13-1660893638679.png

15. Add a "Compose" action putting the following expression, useful to identify the max date from an integer:

addDays('1900-01-01',variables('varMax'),'yyyy-MM-dd')

MarconettiMarco_14-1660893707884.png

16. Add the "Set variable" action, in order to set the temporary variable empty:

[]

MarconettiMarco_15-1660893769281.png

17. Add an "Append to string variable" action in order to populate the varEmployee variable:

add the Current item: outputs from Compose-MinDate - outputs from Compose-MaxDate followed by the "<br>" code:

MarconettiMarco_16-1660893802947.png

18. Outside the Apply to each control, add a "Send an email (V2) action, in the email body add the "varEmployee" variable.

MarconettiMarco_17-1660893901667.png

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

BR,

Marco

 

 

 

 

 

 

 

 

View solution in original post

2 REPLIES 2
MarconettiMarco
Super User
Super User

Hello @DynamicsHS ,

please follow these steps:

1. Initialize a String variable, that will be useful to list the Employees in the email body:

MarconettiMarco_0-1660892835159.png

 

2. Initialize a temporary Array variable, to be used to list the dates as numbers and find min and max values:

MarconettiMarco_1-1660892880215.png

 

3. Initialize an Integer variable, to store the min value:

MarconettiMarco_2-1660892917901.png

4. Initialize an Integer variable to store the max value:

MarconettiMarco_3-1660892954932.png

5. Add the Excel "List rows present in a table" in order to grab the Employees rows:

MarconettiMarco_4-1660892992135.png

6. Add a "Select" action where we want to extract the Employee from the previous array:

MarconettiMarco_5-1660893014204.png

7. Add a "Compose" action in which put the following expression, useful to get only unique values from the Select outputs action:

union(body('Select'),body('Select'))

MarconettiMarco_6-1660893097499.png

8. Add an "Apply to each" control where add the following actions. The "Select an output from previous steps" field must be filled with the previous Compose outputs action:

MarconettiMarco_7-1660893128688.png

In the "Apply to each" settings it's needed to enable the "Concurrency control" and set it to 1.

MarconettiMarco_19-1660894044894.png

 

MarconettiMarco_18-1660894018975.png

 

9. Add a "Filter array" action where we want to filter the excel rows based on the employee name:

MarconettiMarco_8-1660893210680.png

 

10. Add a "Parse JSON" action, in the "Content" field put the Filter array Body. In regards of the Schema section, please first run the flow, copy the Filter array outputs, then click on the "Generate from sample" button and paste the code inside.

MarconettiMarco_9-1660893235488.png

11. Add an Apply to each loop, putting the Parse JSON Body. Inside the Apply to each control, add the "Append to array variable", in the Value add the following expression in order to convert the string date in an integer:

int(items('Apply_to_each_2')['Date'])

MarconettiMarco_10-1660893381006.png

12. Add a "Set variable" action where select the varMin variable and add the following expression:

min(variables('varNumbers'))

MarconettiMarco_11-1660893494713.png

13. Add a "Compose" action putting the following expression, useful to identify the min date from an integer:

addDays('1900-01-01',variables('varMin'),'yyyy-MM-dd')

MarconettiMarco_12-1660893553758.png

14. Add a "Set variable" action where select the varMax variable and add the following expression:

max(variables('varNumbers'))

MarconettiMarco_13-1660893638679.png

15. Add a "Compose" action putting the following expression, useful to identify the max date from an integer:

addDays('1900-01-01',variables('varMax'),'yyyy-MM-dd')

MarconettiMarco_14-1660893707884.png

16. Add the "Set variable" action, in order to set the temporary variable empty:

[]

MarconettiMarco_15-1660893769281.png

17. Add an "Append to string variable" action in order to populate the varEmployee variable:

add the Current item: outputs from Compose-MinDate - outputs from Compose-MaxDate followed by the "<br>" code:

MarconettiMarco_16-1660893802947.png

18. Outside the Apply to each control, add a "Send an email (V2) action, in the email body add the "varEmployee" variable.

MarconettiMarco_17-1660893901667.png

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

BR,

Marco

 

 

 

 

 

 

 

 

DynamicsHS
Resolver I
Resolver I

@MarconettiMarco .....Wow, so in-depth. Thank you so much! 

 

You sir are a legend. 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (3,279)