How to retrieve the last business day of the specified month.(Power Automate Cloud flows)

03/11/2024

Japanese version.

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.

ColumnValue
No1
DateAny 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.

ColumnValue
Location
Document Library
File
Table
Excel book placement and table names.
Key ColumnNo
Key Value1
DateThe 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.

For those who want to learn Power Automate Cloud flow effectively

The information on this site is now available in an easy-to-read e-book format.

Or Kindle Unlimited (unlimited reading).

You willl discover how to about basic operations.

By the end of this book, you will be equipped with the knowledge you need to use Power Automate Cloud flow to streamline your workflow.

Links

Tips.(Power Automate Cloud flows)