Posted by I had a question been asked on how to calculate the last day of the month, taking into account weekends. Here is my version. You can adjust the logic as needed based on your specific requirements and any regional differences in working days.
What is the use case?
Calculating the last working day of the month in Power Automate offers a practical solution for various business needs. From ensuring timely financial transactions and reporting to meeting project deadlines and regulatory compliance, knowing the last working day facilitates streamlined processes and efficient operations such as sending a report at the end of the month.
In the example below, I calculate the last day by accounting for:
- Weekends (Saturday and Sunday)
- Public holidays (retrieved from an external web service)
- Additional holidays specific to my organization (stored in a SharePoint list or Dataverse; in this example, I use SharePoint)
How to build one using Power Automate
Calculating the last working day of the month in Power Automate involves the following steps
Get the current date
Find the last day
Check if the last day was a weekend
Check if the last day was a public holiday (web service)
Check if the last day was an organisation’s holiday
Return the last date
Compose – Get Current date
utcNow()Compose – Get last day
formatDateTime(subtractFromTime(startOfMonth(addToTime(outputs('Compose_-__Get_current_date'), 1, 'Month')), 1, 'Day'), 'yyyy-MM-dd')
Switch
dayOfWeek(outputs('Compose_-_Get_last_day'))Case – Saturday
addDays(outputs('Compose_-_Get_last_day'),-1)Case – Sunday
addDays(outputs('Compose_-_Get_last_day'),-2)In Power Automate, you can use the “dayOfWeek” function to determine the day of the week for a given date. This function returns a numeric value, where Sunday is 0, Monday is 1, Tuesday is 2, and so on, up to Saturday, which is 6. I subtract days accordingly to find the last working day.
