Case study: Calculating business days with Power Apps

Published on September 22, 2023

Power Apps Case Study

Requirement

The customer has a tracking application. They want to set a certain follow-up date based on a date control but exclude weekends and holidays. 🤔

Research

Never having done this before, the first step is searching the web! Of course, Matthew Devaney had a pretty good article to get me started: Power Apps Calculate Business Days Excluding Weekends & Holidays. His requirement was a little different than ours. He wanted to provide two dates and then calculate the number of business days between those two. I want to pick a date in the future that only counts business days.

Next ten days

My approach was to start with a range of dates beginning with today. For prototyping, I just picked ten dates. I plan to filter it down until I arrive at my target date, which does not include weekends or holidays.

This creates a collection with a sequential list of the following ten dates.

ClearCollect(
    col_NextTenDays,
    ForAll(
        Sequence(10),
        DateAdd(DateAdd(Today(),0),Value)
    )
);

Ok, it's a start!

Select weekdays

Now, I need to select only the weekdays. Luckily, Microsoft has a function called Weekday() that allows us to do some filtering easily!

I will chain collections together for debugging purposes, filtering at each step. That makes sense to my brain, at least! This is what my button does now:

ClearCollect(
    col_NextTenDays,
    ForAll(
        Sequence(10),
        DateAdd(DateAdd(Today(),0),Value)
    )
);
ClearCollect(
    col_DaysWeekendsRemoved,
    Filter(
        col_NextTenDays,
        Weekday(Value) in [2,3,4,5,6]
    )
);

Remove holidays

The dates for holidays will change from year to year. The production solution will need to have some way to specify the holidays observed. A SharePoint list would work well for this solution, but for testing, let's define a holiday manually and see if we can filter it out of our collection.

The first collection is our Holiday collection. The second collection is filtering our dates collection further. Not(Value in col_Holidays) means when Value (our dates) is not in the Holidays collection.

ClearCollect(
    col_NextTenDays,
    ForAll(
        Sequence(10),
        DateAdd(DateAdd(Today(),0),Value)
    )
);
ClearCollect(
    col_DaysWeekendsRemoved,
    Filter(
        col_NextTenDays,
        Weekday(Value) in [2,3,4,5,6]
    )
);
ClearCollect(
    col_Holidays,
    Table(
        {Value: DateValue("09/26/2023")}
    )
);
ClearCollect(
    col_DaysWeekendsAndHolidaysRemoved,
    Filter(
        col_DaysWeekendsRemoved,
        Not(Value in col_Holidays)
    )
);

Now we're getting close!

Select the target date

Now, we've filtered out all the weekend and holiday dates from our collection. Let's select the target business day when our request is due!

Let's select the third business from today, 09/22/2023.

ClearCollect(
    col_NextTenDays,
    ForAll(
        Sequence(10),
        DateAdd(DateAdd(Today(),0),Value)
    )
);
ClearCollect(
    col_DaysWeekendsRemoved,
    Filter(
        col_NextTenDays,
        Weekday(Value) in [2,3,4,5,6]
    )
);
ClearCollect(
    col_Holidays,
    Table(
        {Value: DateValue("09/26/2023")}
    )
);
ClearCollect(
    col_DaysWeekendsAndHolidaysRemoved,
    Filter(
        col_DaysWeekendsRemoved,
        Not(Value in col_Holidays)
    )
);
ClearCollect(
    col_TargetDate,
    Last(FirstN(col_DaysWeekendsAndHolidaysRemoved,3))
)

This code first uses the FirstN() function to select the first three business days in our collection. Then we wrap that in the Last() function. We're selecting the last record of the first three records. 🤓

We now have the third business day from today. Nice.

Stick the landing

We could leave the code strictly as-is for readability purposes. But if we wanted to remove the overhead of creating and storing the collections, we could do all this without them. We can collapse each step into the next step to remove the collection statements. Let me demonstrate.

ClearCollect(
    col_NextTenDays,
    ForAll(
        Sequence(10),
        DateAdd(DateAdd(Today(),0),Value)
    )
);
ClearCollect(
    col_DaysWeekendsRemoved,
    Filter(
        col_NextTenDays,
        Weekday(Value) in [2,3,4,5,6]
    )
);

The block above is the code that creates our first two collections. The ForAll() block of the first ClearCollect() can be placed directly into the second ClearCollect() statement in place of col_NextTenDays in the filter statement, and we can remove the first ClearCollect() statement. We now have one collection instead of two.

ClearCollect(
    col_DaysWeekendsRemoved,
    Filter(
        ForAll(
            Sequence(10),
            DateAdd(DateAdd(Today(),0),Value)
        ),
        Weekday(Value) in [2,3,4,5,6]
    )
);

I continued this process of reducing, and replaced the final ClearCollect() function with a Set() function since we're getting a single value instead of a collection of values.

Set(
    gbl_TargetDate,
    Last(
        FirstN(
            Filter(
                Filter(
                    ForAll(
                        Sequence(10),
                        DateAdd(
                            DateAdd(
                                Today(),
                                0
                            ),
                            Value
                        )
                    ),
                    Weekday(Value) in [
                        2,
                        3,
                        4,
                        5,
                        6
                    ]
                ),
                Not(Value in col_Holidays)
            ),
            3
        )
    )
)

This is almost production-ready code. The code's Today() function would reference a Power App date control. We could also make the value for the number of business days configurable. In this code, I hard-coded it to 3. Finally, we'd build our holiday collection based on a SharePoint list maintained by the business owners.

This code then could be assigned to the OnChange for a date control, and the gbl_TargetDate variable could be used during the Patch() command.

This was a fun little challenge. Would you approach this differently? How so?

Thanks for reading.

Reply by email

---