How to retrieve the last business day of the specified month.(Power Automate Cloud flows)
Steps
Power Automate alone is too much work, so we will utilize an Excel sheet.
First, prepare a "Holidays" sheet in an Excel book and list all holidays except Saturdays and Sundays in column A.
Also, the header rows are omitted and the first row is the data row.
Create a sheet for the following calculations and convert it to a table.
| Column | Value |
|---|---|
| No | 1 |
| Date | Any value. |
| Formula | =TEXT(WORKDAY(DATE(YEAR(EDATE(B2,1)), MONTH(EDATE(B2,1)),1),-1,Holidays!A:A),"yyyy-MM-dd") |
This Excel book should be in OneDrive or SharePoint, where it can be accessed by Power Automate.
After saving the Excel book, create a Power Automate flow.
Next to the trigger, place the "Update a Row" action and set it up as follows.
| Column | Value |
|---|---|
| Location Document Library File Table | Excel book placement and table names. |
| Key Column | No |
| Key Value | 1 |
| Date | The date of the month for which you want to calculate the last business day. |
The last business day is stored in the dynamic content "Formula" of the "Update a Row" action.
---