While working with Power Automate, I recently encountered a scenario where I needed to calculate the last working day of a month. This is particularly useful for generating monthly reports, scheduling reminders, or automating month-end processes.
In this tutorial, I will explain how to calculate the last working day of a month using Power Automate.
Calculate the Last Working Day Of a Month Using Power Automate
Here, I need to create an instant cloud flow in Power Automate that calculates the last working day of a month. When I run the flow, it prompts me to enter a date. Based on the entered date, the flow will be calculated and returned on the last working day of that month.
Let’s see how to create:
1. Open the Power Automate home page, click + Create, select the Instant cloud flow, enter the flow name, and choose the trigger’s flow (manually trigger the flow).
- Add an input as ‘Date‘ to this trigger, as shown below.
2. Now I won’t enter a date next month, so I add an Add to time action and provide below parameter:
- Base Time: Select the Trigger date from dynamic content.
- Interval: Here, I want next month, so I put 1.
- Time Unit: Select Month from the drop-down.
3. Then, I add an Initialize variable action, which stores our last working day as a string variable and leaves the value parameter blank. Here, I change the action name to make it easier to identify.
4. I added an ‘Initialize Variable’ action to store the current month’s end date. Whatever date we input, it saves the month’s end date. To achieve this, we need two functions: startOfMonth and addDays. Using startOfMonth, we can get the start date of the current month, and with addDays, we subtract one day to get the current month’s end date.
The full expression for this is:
addDays(startOfMonth(body('Add_to_time_|_NextMonth')),-1,'MM-dd-yyyy')
This will always give us the last day of the current month, but that’s not all. We also need to check which day the last date is.
5. Then I added another Initialize Variable action with the help of the below parameter:
- Name: I gave the name varDayOfWeek.
- Type: Select an integer from the drop-down
- Value: click the expression and add the below expression:
dayOfWeek(variables('varCurrentMonthEndDate'))
This gives us the day numbers Monday-1, Tuesday-2, Wednesday-3, Thursday-4, Friday-5, Saturday-6, Sunday-0.
6. Now I add a switch case action, and in the On parameters, I give the varDayOfWeek Initialize Variable. Check the screenshot below.
If the CurrentMonthEndDate falls on a Monday (1), we subtract 3 days; if it’s a Tuesday (2), we subtract 4 days; if it’s a Wednesday (3), we subtract 5 days; if it’s a Thursday (4), we subtract 6 days; if it’s a Friday (5), we subtract 0 days; if it’s a Saturday (6), we subtract 1 day; and if it’s a Sunday (0), we subtract 1 day.
7. For each case, add a compose action and put the expression:
| Case | Expresion |
|---|---|
| Case 1 | addDays(variables(‘varCurrentMonthEndDate’),-3,’MM-dd-yyyy’) |
| Case 2 | addDays(variables(‘varCurrentMonthEndDate’),-4,’MM-dd-yyyy’) |
| Case 3 | addDays(variables(‘varCurrentMonthEndDate’),-5,’MM-dd-yyyy’) |
| Case 4 | addDays(variables(‘varCurrentMonthEndDate’),-6,’MM-dd-yyyy’) |
| Case 5 | addDays(variables(‘varCurrentMonthEndDate’),0,’MM-dd-yyyy’) |
| Case 6 | addDays(variables(‘varCurrentMonthEndDate’),-1,’MM-dd-yyyy’) |
| Case 7 | addDays(variables(‘varCurrentMonthEndDate’),-2,’MM-dd-yyyy’) |
In the same way, add the set variable and set the varLastWorkingDay with the compose action output in each case.
Once the Flow is ready, we will run the Flow.
Run the Flow to Calculate the Last Working Day of the Month
Now, it’s time to save the flow. Click on ‘Test’ -> Select ‘Manually.’ It will ask for a date; I entered 2nd September 2024, then clicked ‘Run flow.’ As you can see, the last Friday is 27th September 2024.
After running the flow, you can see that the compose action shows 09-27-2024.
In this tutorial, we learned how to calculate the last working day of a month using Power Automate. This method is useful for scheduling reminders, generating monthly reports, and other processes that rely on knowing the last working day.
Also, you may like some more Power Automate tutorials:
- Convert HTML to PDF Using Power Automate [Without using Premium Connector]
- Get Start and End of Month in Power Automate
- Get Current and Previous Months in Power Automate
- get days of month in Power Automate
After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.
