Get last Sunday of month

[WebPortal]

Hello,

I'm building a flow that runs on the 1st day of each month.

How do I get the date of the last Sunday of that month?

Can anyone help?

Thanks in advance!

[ChristianAbata]

hi @WebPortal  mmm by using power automate expressions I think is not possible but you can try by looking for an API that can give you all the days from a month and then you can use the expression last index of to see what is the last Sunday of that month

[WebPortal]

@ChristianAbata really? Such a trivial task should be easy.

Well, then I guess I'll have to create an Excel file and get all the info I need into PA variables.

[ChristianAbata]

Yes @WebPortal  you can do that or make all your info into sharepoint lists.

[abm]

Hi @WebPortal 

This is possible. I will send you something soon.

Thanks

[abm · Accepted solution]

Hi @WebPortal 

There is a cookbook article https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Find-Days-in-a-month-and-end-of-the-mont... regarding finding the number of days in a month and last date of the month. Utilising those logic I have extended the logic to find the last Sunday of the month. Here are the steps I followed. If needed please email me your address I will export the solution for you.

Think about building the below logic as a child flow passing todays date. Child flow will return the last Sunday of the month. 

I can warn you there are quite a lot of steps 🙂 Following are the steps:

Above expressions are:

To find the current year 

int(formatDateTime(variables('mydate'), 'yyyy'))

To find the current month

int(formatDateTime(variables('mydate'), 'MM'))

Next we need to find whether the year is a leap year or not. Then find the days in month.

Above expression to find the leap year

or(and(equals(mod(variables('yr'), 4), 0), not(equals(mod(variables('yr'), 100), 0))), equals(mod(variables('yr'), 400), 0))

To find days in month

if(equals(variables('mnth'),2), if(outputs('IsLeapYr'), 29, 28), if(or(equals(variables('mnth'),1), equals(variables('mnth'),3), equals(variables('mnth'),5), equals(variables('mnth'),7), equals(variables('mnth'),8), equals(variables('mnth'),10), equals(variables('mnth'),12)), '31', '30'))

Next using the above values construct the last month date and store in a variable.

Note: Above the 'current month last date' step the year - month - output (here the output is the previous compose step Days in Month). Next CurrDate is output is mapped from previous step Current month last date.

Next steps we need to find which day of the week (Sunday). If not Sunday decrement the date in a loop and check the day of the week in Sunday. For this we need a loop.

Above current date format is as follows:

formatDateTime(variables('CurrDate'),'yyy-MM-dd')

Under the IF condition to find the week day the expression I used is as follows:

formatDateTime(outputs('CurrentDate'),'dddd')

Decrease Day expression is

addDays(formatDateTime(variables('CurrDate'),'yyyy-MM-dd'),-1)

Set variable 2 (CurrDate) output is mapped from the previous step Decrease Day.

Finally outside the while loop to get the last Sunday of the month

Expression is as follows:

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

Final flow preview is as follows:

My test run results:

1

Input date : 11/01/2020 (mm/dd/yyyy format)

Thanks

2.

Input date : 10/15/2020 (mm/dd/yyyy format)

3.

Thanks

[WebPortal]

OMG with all these steps we will never automate anything!

😁😁

[abm]

Hi @WebPortal 

hahaha. Thats the reason I suggested to create a child flow to create this like a sub function. Let me know how it goes.

Thanks

[WebPortal]

Ok, I had a look at all those steps and they seem to make sense.

I'll need a second flow to find the last Saturday, that should be identical.

So, I guess I'll have to explore the concept of "sub-flow" in the documentation.

Can you export your flow and attach it here? That would help me on the implementation.

Thank you!

[abm]

Hi @WebPortal 

Can't attach any attachments via community posts. Send me ypur email as  private message. 

To find Saturday all you need to change is the IF condition where it checks 'Sunday' to Saturday'.

There are lots of articles in community regarding how to create child flows.

Thanks

[WebPortal]

Thanks for helping!

I now realize that child flows require a Premium license, is that so?

[abm]

Hi @WebPortal 

No you don't need premium license. Please see https://flow.microsoft.com/en-us/blog/helpful-tips-for-using-child-flows/

You need to create a solution. Then include your current flow into that solution. Then create a child flow. Child flow can be created under solutions only.

Thanks

[WebPortal]

@abm thanks for your help.

Now, I'll have to study what "Solutions" are... it's a long way until I manage to find that date..

[abm]

Hi @WebPortal 

I could quickly do a screen share and educate you if that helpful. It's super easy and solutions are best way to delivery flow to multiple environments.

Thanks