cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vffdd
Responsive Resident
Responsive Resident

Adding Business days to a date

Trying to find either a solution of workaround for this..

 

I want to add a set number of Business days to a date.    So this number varies a lot so I can't really hard code it in.   for example I choose X in a drop down then the number of days to add is 10  I choose Y the number of days to add is 12 etc.. there are a lot of variations.   

 

The number of days is in a txt box and at the moment I just do a simple  addition formula.  but that's obviously a full 10 days or 12 days and I only want to add business days !!  

 

Any suggestions?

 

TIA

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-siky-msft
Community Support
Community Support

Hi @vffdd ,

 

Please refer to the answer of this thread.

If the first day of week is Sunday, please try this, just replace the 'TextBox.Text'

    DateAdd(
        Now(),
        Value(TextBox.Text) + 2 * If(
            (Weekday(
                Now(),
                StartOfWeek.Sunday
            ) + Value(TextBox.Text) > 5,
            RoundDown(
                (Value(TextBox.Text) - (5-Weekday(
                    Now(),
                    StartOfWeek.Sunday
                ))) / 5,
                0
            ) + 1
        )
    )

If the first day of week is Monday, please try this:

    DateAdd(
        Now(),
        Value(TextBox.Text) + 2 * If(
            (Weekday(
                Now(),
                StartOfWeek.Monday
            ) + Value(TextBox.Text)) > 5,
            RoundDown(
                (Value(TextBox.Text) - (5-Weekday(
                    Now(),
                    StartOfWeek.Monday
                ))) / 5,
                0
            ) + 1
        )
    )

 Hope this helps.

Sik

View solution in original post

3 REPLIES 3
SkiDK
Solution Sage
Solution Sage

Hi @vffdd 

Here's a very good article that shows how to take only in account working days, thus without weekends and holidays:

https://powerapps.microsoft.com/es-es/blog/excluding-weekends-and-holidays-in-date-differences-in-po...

Hope it helps!

v-siky-msft
Community Support
Community Support

Hi @vffdd ,

 

Please refer to the answer of this thread.

If the first day of week is Sunday, please try this, just replace the 'TextBox.Text'

    DateAdd(
        Now(),
        Value(TextBox.Text) + 2 * If(
            (Weekday(
                Now(),
                StartOfWeek.Sunday
            ) + Value(TextBox.Text) > 5,
            RoundDown(
                (Value(TextBox.Text) - (5-Weekday(
                    Now(),
                    StartOfWeek.Sunday
                ))) / 5,
                0
            ) + 1
        )
    )

If the first day of week is Monday, please try this:

    DateAdd(
        Now(),
        Value(TextBox.Text) + 2 * If(
            (Weekday(
                Now(),
                StartOfWeek.Monday
            ) + Value(TextBox.Text)) > 5,
            RoundDown(
                (Value(TextBox.Text) - (5-Weekday(
                    Now(),
                    StartOfWeek.Monday
                ))) / 5,
                0
            ) + 1
        )
    )

 Hope this helps.

Sik

View solution in original post

vffdd
Responsive Resident
Responsive Resident

Perfect thanks a million @v-siky-msft 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,379)