Power Automate get Date

[ecscoza]

Hi There

I am building a simple power automate flow that have the Sharepoint list with the following field:

Contract Start Date - eg data: 15/07/2021

Contract End Date - eg data: 31/08/2021

Contract Duration - eg data: This should be End date - Start date to provide in months or days, like : 1 month, 16 days (47 days)

Service Intervals - eg data: Weekly

Next_Service_Date - eg data: 15/07/2021

Now more detail on Service Intervals, its a dropdown selection with the intervals from the Contract Start Date, which is Thursday 15 July 2021

Weekly - the next first date from the next week from Next_Service_Date, should be Monday 19 July 2021 then Monday 26 July 2021 and so on
Monthly - the next first date from the next month from Next_Service_Date, should be Sunday 01 Aug 2021 the 01 Sep 2021 and so on
Bi Monthly - Every second month from Next_Service_Date, should be Sunday 01 Aug 2021 and then 01 October 2021 and then 01 Dec 2021 and so on
Quarterly - Once in the beginning of the next quarter from Next_Service_Date, (Q1 Jan, Feb, Mar | Q2 Apr, May, Jun | Q3 Jul, Aug, Sep | Q4 Oct, Nov, Dec ) should be Q4 which will be 01 Oct 2021 and then Q1 01 Jan 2021 and Q2 01 Apr 2022 and so on
Bi Annual - Once in the beginning of the next half of the year from Next_Service_Date, (First Half: Jan, Feb, Mar, Apr, May, Jun | Second Half: Jul, Aug, Sep, Oct, Nov, Dec ) should be First Half which will be 01 Jan 2022 and then Second Half 01 Jul 2022 and so on
Annually - Once a year, from the Next_Service_Date, so the next should be 15 Jul 2022
Biannual (every 2 yrs) - Every second year from Next_Service_Date, should be 15 Jul 2022 and then 15 Jul 2024 and so on
Quinquennial (every 5 yrs) - Every five year from Next_Service_Date, should be 15 Jul 2022 and then 15 Jul 2026 and so on
So I want based on the item dropdown selection to calculate and output the next date into a variable on flow and the I can update SP item field name: Next_Service_Date with the variable output.

Then the plan the flow to pause and wait until this new date or check once a day with the current date for the next new service date based on the selection above

How can i do then, please explain step by step

ECSCOZA

[v-albai-msft · Accepted solution]

Hi @ecscoza,

Based on your information, I assume that:

1. The default Next_Service_Date is the next Monday based on Contract Start Date.
2. When Today is between Start Date and End Date, the flow will run to update the Next_Service_Date.
3. Only when Next_Service_Date is less than Today, the flow will run to update the Next_Service_Date.(For example, the Start Date is 7/30/2021, but Today is 16/7/2021, the flow will not update this date.)

I will give your a demo when update items with "Weekly" selected:

1. Create a recurrence flow to run everyday. 

2. First update all items of their Next_Service_Date based on their Start date:

addDays(items('Apply_to_each_2')?['ContractStartDate'],if(equals(dayOfWeek(items('Apply_to_each_2')?['ContractStartDate']),0),1,sub(8,dayOfWeek(items('Apply_to_each_2')?['ContractStartDate']))),'dd/MM/yyyy')

3. Add a Swith action to check the Service Intervals/Value:

4. When the Service_Intervals/Value equals to Weekly, add a condition, only when Contract_End_Date is greater than Today(utcNow('MM/dd/yyyy')) and Next_Service_Date is less than Today(utcNow('MM/dd/yyyy')), I will update their Next_Service_Date:

5. If meet above conditions, update the Next_Service_Date based on Today(first based on Start Date).

addDays(utcNow(),if(equals(dayOfWeek(utcNow()),0),1,sub(8,dayOfWeek(utcNow()))),'dd/MM/yyyy')

Here is my result:

Refer to my steps and complete your other Swith cases.

Best regards,

Allen