Calculate the last working day of the month

Right, I am going to say it, I don't like dates in Power Automate, they always give me a headache. I don't know why, but they just do.

Leave a comment below if you feel the same as me.

Recently, a friend asked me if it was possible to get the last working day of the month. and I said "Yeah, course it is". Well it is/was but it was a bit of an aforementioned headache.

I found this post - https://debajmecrm.com/how-to-get-last-day-of-current-month-in-power-automate/ - it has helped me get started, but I still wanted to find the last working day of the month.

Imagine the scenario, you want to email something on the last working day of the month, not all last days of the month end on a week day (Monday - Friday), so we have to do some conditional logic in Power Automate to help us calculate this.

I started off by creating a flow - note the new URL - https://make.powerautomate.com/ and I went with an Instant Cloud Flow, then I added an "Add to time" step and renamed it to NextMonth. Here I added one month on to the current time, which I got from using the utcNow() expression, as per the screenshot below:

Next, I initialized two variables:

1. EndOfMonthWorkingDay - we will come back to this one at the end

2. CurrentMonthEndDate

The CurrentMonthEndDate will look at the start of the following month based on the NextMonth date from the step above and then subtract 1 day, the expression for this is:

addDays(startOfMonth(body('NextMonth')),-1,'MM-dd-yyyy')

This will always give us the last day of the current month, but that's not all, we also need to check if it is a Monday - Friday or a Saturday/Sunday.

If the CurrentMonthEndDate is a Monday, we subtract 2 days from that date and then set the output of that compose step to the EndOfMonthWorkingDay variable, as shown in the screenshot below. Job done for Mondays !

For the Condition: If Day of Week Equals Monday part the expression is:

dayOfWeek(variables('CurrentMonthEndDate')) = 1

For the Compose step, the expression is:

addDays(variables('CurrentMonthEndDate'),-2)

We do this because if the first day of the next month is a Monday, then we need to go back 2 days to cater for the weekend in between, resulting in the Friday before.

If the CurrentMonthEndDate is not a Monday, and it is a Tuesday - Friday, then we just need to set the EndOfMonthWorkingDay variable to the CurrentMonthEndDate, this is catered for in our no branch of the condition above:

Finally, we just need to check if the CurrentMonthEndDate is a Saturday or a Sunday, and this is done in the no branch of this condition:

If the CurrentMonthEndDate is a Saturday, we subtract 1 day, if it's not a Saturday (i.e. a Sunday) then we subtract 2 days.

Leave a comment if you don't like dates as much as I do :)

As always my email is always open for any questions - jon@jondoesflow.com