Answered

Adding 24 hours to a date but skipping weekends and holidays provided in a table

(1) Share

Report

Posted on by Dextoaar

146

Hello there,

I was wondering if anyone has any thoughts on how I could add 24 hours to a date/time but skip over a list of holidays I have stored in a dataverse table. It could easily be an excel table or something else too.

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (2)

Verified answer

trice602 15,404 Super User 2025 Season 2 on at

Like (1)

Report

Hi @dextoar ,

You can certainly to the Dataverse table route but might be overkill unless your holidays are already there. Because the number of holidays most likely are less than 10, you could go this route with a conditional action OR my next plan would be an excel table and I will describe that at the bottom. Here's the conditional action option:

My first compose get's the day or week in this format 'ddd' such as Sat or Sun.

formatDateTime(body('Current_time'),'ddd')

I then check if the DOW is Sat or Sun in my condition shown below:

Then to show the Holiday option inside the condition action, I threw in a couple of dates so essentially if this is true, add 24 hours to my date, otherwise skip.

addHours(body('Current_time'),24)

The formatDatetime expressions below is:
formatDateTime(utcNow(),'MM-dd-yyyy')

Give this a try and run it a couple of times, with today as a Holiday but this is one option.

The Excel option:

A simple table with Date column and also is the key column. if the body of the output is > 0 then it's not a Holiday.

length(body('Get_a_row'))

That said, you have a couple of options to consider. My preference is just the conditional action.

Get row in a table

Was this reply helpful? Yes No
Verified answer

Linn Zaw Win 2,996 on at

Like (2)

Report

@dextoar

There is a blog post on how to add working days (just 1 for your case) excluding weekends and Public Holidays (maintained in the SharePoint list but it will work for any data source as long as it is mapped in HolidayDays Select step)

https://www.tachytelic.net/2022/04/power-automate-flow-add-working-days-date/

Here's a video tutorial

https://www.youtube.com/watch?v=zPKEGT03AZE

Other blog posts

https://devoworx.net/how-power-automate-add-working-days-to-date-correct-solution/

https://www.penthara.com/how-to-dynamically-calculate-working-business-days-for-a-due-date-in-power-automate/

Was this reply helpful? Yes No
Dextoaar 146 on at

Like (1)

Report

Appreciate both of your thoughts here. They were helpful in how to approach. I ended up connecting to an excel, running a workdays formula, and returning the formula into the table.

Was this reply helpful? Yes No
trice602 15,404 Super User 2025 Season 2 on at

Like (0)

Report

Hi @dextoar ,

Glad you got something that works! If both of these posts were helpful and viable solutions, please consider marking both as a solution to help others find these options too!

Was this reply helpful? Yes No