How to get the last business day of the specified month.(Power Automate Desktop)
Introduction to how to get the last business day of a given month in Power Automate Desktop.
Overall view of Flow
Robin(for copy and paste)
It can be copied and pasted into Power Automate Desktop.
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Test\\last_business_day_e.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet1''' DateTime.GetCurrentDateTime.Local DateTimeFormat: DateTime.DateTimeFormat.DateOnly CurrentDateTime=> CurrentDateTime Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: CurrentDateTime Column: 1 Row: 1 Excel.ReadFromExcel.ReadCell Instance: ExcelInstance StartColumn: 2 StartRow: 1 ReadAsText: False CellValue=> ExcelData Excel.CloseExcel.Close Instance: ExcelInstance
Flow creation steps
Power Automate Desktop alone is labor intensive, so we will utilize Excel sheets.
First, prepare a Holidays sheet in the Excel book and list all holidays other than Saturday and Sunday in column A.Data rows from line 1. Header lines are not required.
Next, enter the following formula in cell B1 of the sheet for calculations
The last business day of the month for the date entered in cell A1 will now be displayed in cell B1.
After saving the Excel book, create a Power Automate Desktop flow.
Set up an Excel Launch Action and specify an Excel book.
|Launch Excel||an open the following document|
|Document path||Saved Excel file.|
|Make instance visible||OFF|
|Open as ReadOnly||OFF|
Set up an Set active Excel worksheet Action and specify the sheet with the formula.
Set up a Write to Excel worksheet Action.
The Value to Write can be a textual value of the year and month for which you want to retrieve the last business day, or a Datetime variable that can be retrieved with the Get current date and time Action.
|Value to write||The year and month for which you wish to obtain the last business day.|
Any number of days is acceptable as long as the year and month are correct.
|Write mode||On specified cell|
Set up a Read from Excel worksheet Action to retrieve the last business day.
|Retrieve||The value of a single cell|
Close the book by placing a Close Excel Action.
When this flow is executed, the Read from Excel worksheet Action. The last business day is set to the Variable produced. Variable name ExcelData by default.