cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate I
Advocate I

Excel Workdays Function Work Around?

Hey, Im trying to create an Event planner that can book a series of events based on a work day in a month on U reference . 

so im looking to set up a meeting say on the the 10th working day of the month or 10 days back from the last working day of the month.

 

So if U = End Of months then this would be 31/07/2020  and - 10 workdays would be 17/07/2020 or +10 would be 14/08/2020

 

I found this post here but count seem to adapt it. 

https://powerusers.microsoft.com/t5/Building-Power-Apps/Adding-working-days-and-hours-to-a-date/td-p...

At first i thought i had it working but then i checked a few months ahead and it glitched 😞 

 

Copy of the appi is attached 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @unislacker :

I assume that working days are from Monday to Friday of the week (if you want to calculate holidays, you can add conditions),I'v made a test for your reference:

1\Add a button and set it's OnSelect property to:

 

ClearCollect(mycollection,/*mycollection is my custom collection*/
             ForAll([1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16],
                    If(!(Weekday(DateAdd(Date(2020,7,31),Value,Days)) in [1,7]),               
                       {Date:DateAdd(Date(2020,7,31),Value,Days)}
                       )
                   )
             )

 

2\Add a lablel and set it's Text property to:

 

Last(FirstN(mycollection,10)).Date

 

1.JPG

Best Regards,

Bof

View solution in original post

5 REPLIES 5
Community Support
Community Support

Hi @unislacker :

I assume that working days are from Monday to Friday of the week (if you want to calculate holidays, you can add conditions),I'v made a test for your reference:

1\Add a button and set it's OnSelect property to:

 

ClearCollect(mycollection,/*mycollection is my custom collection*/
             ForAll([1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16],
                    If(!(Weekday(DateAdd(Date(2020,7,31),Value,Days)) in [1,7]),               
                       {Date:DateAdd(Date(2020,7,31),Value,Days)}
                       )
                   )
             )

 

2\Add a lablel and set it's Text property to:

 

Last(FirstN(mycollection,10)).Date

 

1.JPG

Best Regards,

Bof

View solution in original post

Super User
Super User

There may be a more efficient way to do this, but I have a way that works. this also could be better with the new Sequence formula, but that isn't in my environment yet.

 

So I add a date picker for the date to calculate business days from

Add a text input field set to number format for the number of business days from date to calculate

Add the following on change of the date picker

ClearCollect(
    DateRange,
    AddColumns(
        FirstN(
            [
                0,
                1,
                2,
                3,
                4,
                5,
                6,
                7,
                8,
                9,
                10,
                11,
                12,
                13,
                14,
                15,
                16,
                17,
                18,
                19,
                20,
                21,
                22,
                23,
                24,
                25,
                26,
                27,
                28,
                29,
                30,
                31,
                32
            ],
            DateDiff(
                DatePicker1.SelectedDate,
                DatePicker1.SelectedDate + 90,
                Days
            ) + 1
        ),
        "Day",
        "Day " & (Value + 1),
        "Date",
        DateAdd(
            DatePicker1.SelectedDate,
            Value,
            Days
        ),
        "Week_Date",
        Weekday(
            DateAdd(
                DatePicker1.SelectedDate,
                Value,
                Days
            )
        )
    )
)

this gives us a collection of 90 calendar days from the specified date.

Then I add a gallery to the canvas and set the items property to 

Filter(DateRange,Week_Date>1,Week_Date<7)

Set the visible property of the gallery to false--we don't need to show it , we just use that to get a filtered list of days excluding Saturday and Sunday.

Then where you want to get the day after the 

business date that falls on the specified day interval, use the following formula:

Last(FirstN(Gallery2.AllItems, Value(TextInput1.Text)+1 )).Date

 

 

 

 

date.png

@unislacker  if this answers your question, please mark it as a solution.

 

Hey @v-bofeng-msft  you got me half way there but i needed to be able to link the the U Date drop down to the newly created list for either post or neg items. Therfore i used what you did with some adaptions.

See final attached for adaptions. Next i will continue to develop the Calendar post function 

Thanks  

Sorry but i didnt want to introduce a date picker Thanks 

@unislacker @My approach also works without a date picker. You can substitute the date value for the Datepicker1.SelectedDate

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Top Solution Authors
Top Kudoed Authors
Users online (89,871)