How to get the number of business days within a specified period.(Power Automate Desktop)
Introduction to how to get the number of business days for a specified period 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\\business_days_period_e.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet1'''
SET FromDate TO $'''2023/2/1'''
SET ToDate TO $'''2023/2/10'''
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: FromDate Column: 1 Row: 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: ToDate Column: 2 Row: 1
Excel.ReadFromExcel.ReadCell Instance: ExcelInstance StartColumn: 3 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
=NETWORKDAYS(A1,B1,Holidays!A:A)
If you enter the start date in cell A1 and the end date in cell B1
The number of business days for that period will be ready to be displayed in cell C1.
After saving the Excel book, create a Power Automate Desktop flow.
Set up an Excel Launch Action and specify an Excel book.
| Parameter | Value |
|---|---|
| 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 is the starting date for the business day calculation. Specify a date/time variable that can be retrieved via text or a Get current date and time Action.
| Parameter | Value |
|---|---|
| Value to write | Start date of business day calculation. |
| Write mode | On specified cell |
| Column | 1 |
| Row | 1 |
Set up a Write to Excel worksheet Action.
The value to be written is the end date of the business days calculation.
It can be either text or a date and time variable that can be retrieved with the The value to be written is a date and time variable that can be retrieved with the Get current date and time Action.
| Parameter | Value |
|---|---|
| Value to write | End of business day calculation. |
| Write mode | On specified cell |
| Column | 2 |
| Row | 1 |
Set up a Read from Excel worksheet Action to retrieve the number of business days.
| Parameter | Value |
|---|---|
| Retrieve | The value of a single cell |
| Column | 3 |
| Row | 1 |
Close the book by placing a Close Excel Action.
When this flow is executed, the Read from Excel worksheet Action. The number of business days is set to the Variable produced. Variable name ExcelData by default.
For those who want to learn Power Automate Desktop 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 Desktop to streamline your workflow.