Excel Workdays Function Work Around?

[unislacker]

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 

[v-bofeng-msft · Accepted solution]

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

Best Regards,

Bof

[jlindstrom]

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

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

[unislacker]

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  

[unislacker]

Sorry but i didnt want to introduce a date picker Thanks 

[jlindstrom]

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