We recently had a problem where a customer needed to know whether a given date fell within the next five working days. After going through some wranglings with Power Automate formulas, we came upon a much simpler solution based on a SharePoint list of workdays.
To set things up, we created a simple SharePoint list of two columns:
Date (date only)
Workday (Y/N)
Updating our SharePoint list, we added the calendar days and marked them with a check (signifying 'Y') if on a workday. Obviously, this step takes a little work to setup, but makes the process so much simpler in the flow.
Then we created a flow in Power Automate. In the flow, we initialize a string variable with a message stating that the validation date "is not within 5 working days of the request". Then, we use a Get items step pointing to the SharePoint list to select the first 6 items from the list greater than or equal to the request date that are working days. (We want to ensure that we include the request date as well as the next 5 working days.)
With each of the dates returned, we compare it to a validation date. If the validation date equals any of the request dates returned, we update our variable with the message "is within five working days of the request".
Here's what the initial steps of our flow look like:
We could also use a Boolean variable in the flow, but in our case, we wanted to create a message that we could send in an email.
Once we collect the next five workdays, we run compare the validation date to see if it matches any of the dates. If it matches any of the dates, we update our string variable message to state that the validation date is within the five working day limit.
Finally, we can create an HTML table that we can use in our message or email body by using the Create HTML table step along with a Compose action.
We can use the last Compose step output in the body of an Email to let someone know that the date is valid.
Sometimes creating complex formulas in Power Automate is simply not worth the trouble. With this simple solution, we can solve this problem and move on to the next one!
Comentarios