If you want to use Power Automate to do something on the last work day of the month, then read on! This is a step by step guide on how to achieve this.
Start by creating a scheduled flow, and set it to run each day.
Initiate a string variable called Today, and set the value to today using the expression formatDateTime(utcNow(),’yyyy-MM-dd’). The formatting is important to ensure we can do a comparison later.
Initiate another string variable to calculate the start of the month, and set the value using the expression startOfMonth(utcNow()). This will return the first of the current month, in the UTC format, including hours, minutes and seconds, so something like this 2023-04-01T00:00:00.0000000Z if it was run during the month of April 2023.
Initiate another string variable to calculate the last day of the month. To do this, we’re going to add a month, then substract one second. We do this by using the value from the previous step, and using the expression subtractFromTime(addToTime(variables(‘StartCurrentMonth’),1,’month’),1,’second’).
Initiate an integer variable to evaluate which day of the week the last day of the month is. To do this, use the value from the previous step in the expression dayOfWeek(variables(‘LastDayCurrentMonth’)). This will return an integer from 0 to 6, where 0 means Sunday, 1 means Monday… and 6 means Saturday.
Finally, initiate a string variable where the last work day of the month will be recorded, but do not set an initial value.
The final section will run a series of conditional tests, and the overall structure looks like this.
First, we’re going to use a condition event, to test if the last day of the month is a Sunday. So we are testing if the variable DayOfWeek is equal to 0.
If the day of the week is 0 (Sunday), then we are going to calculate the last work day of the month by subtracting 2 days from that date. This is done by using the expression formatDateTime(subtractFromTime(variables(‘LastDayCurrentMonth’),2,’day’),’yyyy-MM-dd’). Here we are formatting the date to only have year, month and day, so that we can compare it to today later on.
If the day of the week is not 0 (not Sunday), then we are going to use another condition event to test if it is a Saturday. So we are testing if the variable DayOfWeek is equal to 6.
If the day of the week is 6 (Saturday), then we are going to calculate the last work day of the month by subtracting 1 day from that date. This is done by using the expression formatDateTime(subtractFromTime(variables(‘LastDayCurrentMonth’),1,’day’),’yyyy-MM-dd’). Here we are formatting the date to only have year, month and day, so that we can compare it to today later on.
If the day of the week is not 6, at this point, it can only be 1, 2, 3, 4, or 5. In other words, it can only be Monday to Friday, which are work days. This means that the last day of the month is a work day. So we can use this to set the last work day using the expression formatDateTime(variables(‘LastDayCurrentMonth’),’yyyy-MM-dd’). Again, we are formatting the date to only have year, month and day.
Finally, we are going to test if today is the last work day of the month by using a condition event.
After that, you can set the next steps in the If yes branch, and leave the If no branch empty.