Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities - Lets Get Started!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps - Power Apps Experimental Features / Calculating Due Date E...
Power Apps - Power Apps Experimental Features
Suggested answer

Calculating Due Date Excluding Weekends & Holidays

(1) ShareShare
ReportReport
Posted on by CU03062110-0 6
Hello,
 
I'm fairly new to powrapps and I was wondering if someone can please assist.  I've tried googling the answer and I can't find it.  I would like for a date picker control to automatically add 20 work days based on the date of another date picker control which would exclude Weekends and holidays.
 
For example, in my tracker I have two date picker controls called "Request Received" and "Due Date".  I would like for my personnel to input a date in the "Request Received" and then the "Due Date" date picker automatically adds 20 working days excluding weekends and holidays.  So let's say I received a request on 6/2/2025 then my due date will be 7/1/2025 which excluded the weekends and Juneteenth (holiday).
 
I already have the holidays saved in a table within my powerapps.  I tried many different things but I can't get it.  Please help.
  • CU03062110-0 Profile Picture
    CU03062110-0 6 on at
    Calculating Due Date Excluding Weekends & Holidays
     
    I created a table in my power apps called HolidaysTbl, then it has two columns.  One column is called "Holiday" and the other column is called "Holiday Date".
  • CU03062110-0 Profile Picture
    CU03062110-0 6 on at
    Calculating Due Date Excluding Weekends & Holidays
     
    Hi, 
     
    After reading your response, I think I know what I did wrong.  I already had a table with the holidays but it was two column table not one.  One column was the name of the holiday and the second column was the actual date.
     
    Let me go back and correct the table.  I'll let you know how it goes.
  • stampcoin Profile Picture
    stampcoin 2,183 on at
    Calculating Due Date Excluding Weekends & Holidays
    Not quite sure what your meaning is. 
    What I tested: I created a table, with one column, which include some public holiday and weekends.
    I added this month weekend, public holidays and next months weekends. 
    How do you record your holiday/weekend information in the table ?
  • CU03062110-0 Profile Picture
    CU03062110-0 6 on at
    Calculating Due Date Excluding Weekends & Holidays
    Hello, 
    Thank you for the reply but it is not excluding the holidays.  I have my table with the holidays and weekends.  Any advice on what I'm doing wrong?
  • Suggested answer
    stampcoin Profile Picture
    stampcoin 2,183 on at
    Calculating Due Date Excluding Weekends & Holidays
    Assume that you have :
    1. A dataverse table called Holidays.
    2. And column called HolidayDate.
    3. Already added holiday information and weekend information in the table.
    4. Request Received, Date picker control.
    5. Due Date , Date picker control.
    6. If include the “Request Received” date itself as Day 1, if not, use Sequence (35,1)
    Here is the reference example:
    Due Date, DefaultDate=
    With(
    {
        StartDate: 'Request Received'.SelectedDate,
        AllHolidays: Holidays.Holiday
    },
    Last(
        FirstN(
            Filter(
                ForAll(
                    Sequence(35,0),
                    DateAdd(StartDate, Value,TimeUnit.Days)
                ) As ThisDay,
                Weekday(ThisDay.Value, StartOfWeek.Monday) <= 5
                && !(ThisDay.Value in AllHolidays)
            ),
            20
        )
    ).Value
)
     
    Suppose you don’t know in advance how many weekends + holidays will occur in that 20‐workday span, you simply iterate over a slightly larger chunk of calendar days (e.g. 35 or 40 days). That buffer (35–40) is guaranteed to contain at least 20 weekdays once you filter out weekends and holidays.
     
    Use Sequence(35) because in a 5‐week window (35 calendar days), you will always cover at least 20 weekdays even if 3–4 of those are holidays ( hopefully).
    If your want to have more than ~5 holidays in any 28-day span, you can bump Sequence(35) up to Sequence(40) or even Sequence(45). Just make sure it’s large enough that, after filtering out weekends + holidays, you still have at least 20 dates left.
     

    Enjoy Power Platform and have a great day 🚀 |  My LinkedIn

    If the answer helps, please consider 👍, Thanks.

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities - Lets Get Started!

Quick Links

Michael Gernaey – Community Spotlight

We are honored to recognize Michael Gernaey as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard >

Last month Overall leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans