- Dealing with the date is very headache for some time specially for calculate the business days excluding weekends is very tuff for some time and most of the business power apps calculating the business days between two days is a common feature.
- For the Calculate the date fist we get all dates and filtering the weekends and holidays. There is not the direct solution or function available to calculating the number of business days.
- This article will help you to calculating the number of business days.
Introduction: The Leave Requests App
- The Leave Requests App is used by employees at a financial services firm to ask for the vacation sick leave for paid time off.
- For the Leave Request Apps need to be much information like leave status, Approval name Employee Department etc. but here our agenda is the calculated number of businesses days so here we take only necessary columns.
Setup A SharePoint list for leave Requests
- Create a new SharePoint list called Leave Requests with the following columns:
- Employee Name (People Picker)
- Start Date (date only)
- End Date (date only)
- Number Of Days (number)
- Number Of Days (number)
- End Date (date only)
- Start Date (date only)
Create A New Canvas App in Power Apps Studio
- Create new app from blank.
- Insert the Edit form and add button on the screen
- Connect the form with Data Source as Leave Requests.
After the adding data source in edit form. Now you can add the number of days data card to make it view only.
Calculate The Number of Business Days Excluding Weekends in Power Apps
- When Employee enter the Leave request Start Date and End date into the power apps form. For the business day means the Monday – Friday weekdays and not the Company holidays.
- Now we can start the calculate number of business day excluding the weekends.
- For calculating we can use With function and Sequence function for calculating business days.
- Just overview of With function: With holds the value from these functions long enough to be used in further calculations or actions.
- Sequence function: This function is generated single columns table of Sequence numbers like 1,2,3. The name of the column is Value.
- For more info regarding Sequence function: https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-sequence
- For more info, please review the link – https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-with
Write the below code in the default property of the Number of Days DataCard Value.
Note: DataCardValue3(End Date), DataCardValue2(StartDate)
With (
{
// generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence (DataCardValue3.SelectedDate - DataCardValue2.SelectedDate + 1),
DataCardValue2.SelectedDate + Value - 1
)
},
If(
And (
IsBlank(DataCardValue2.SelectedDate),
IsBlank(DataCardValue3.SelectedDate)
),
// show nothing if any date pickers are blank
0,
// include only dates Monday to Friday
CountIf(
varDateRange,
Weekday (Value) in [2, 3, 4, 5, 6]
)
)
)
Setup A SharePoint List for Holiday Calendars
- For the business day calculation, we need to exclude the holidays. So first we capture the number of holidays for the based on the region. For the capturing the holidays we create one list to store all holidays list called “US Holiday List” with the below listed columns.
- Title (single-line text)
- Holiday Day (date only)
| Title | Holiday Day |
| New Year’s Day | 01-01-2022 |
| Epiphany | 01-06-2022 |
| Valentine’s Day | 2/14/2022 |
| Presidents’ Day | 2/21/2022 |
| Employee Appreciation Day | 03-04-2022 |
How to exclude holiday from the business days calculation in power apps
- Now we have added the data source with power apps
- We have already added the calculate business days excluding weekends, so we have enhanced the logic.
- Now we can add the logic on the Default property of the Number of Days DatacardValue. If the holiday date is found within the single columns table being generated it does not get counted in the results.
- Write the below code in the default property of the Number of Days DataCard Value.
Note: DataCardValue3(End Date), DataCardValue2(StartDate)
With (
{
// generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence (DataCardValue3.SelectedDate - DataCardValue2.SelectedDate + 1),
DataCardValue2.SelectedDate + Value - 1
)
},
If (
And (
IsBlank(DataCardValue2.SelectedDate),
IsBlank(DataCardValue3.SelectedDate)
),
// show nothing if any date pickers are blank
0,
// include only dates Monday to Friday
CountIf(
varDateRange,
Weekday (Value) in [2, 3, 4, 5, 6], Not (Value in 'US Holiday List'.HolidayDate)
)
)
)
- For the validate start date & End date are not on Weekends or Holidays for check the
below Criteria.
- The date is not a weekend day (Saturday, Sunday)
- The date is not a holiday
- The start date must be before the end date
- Both a start date and an end date must be selected
For this validate we can add the border colour property of the Start Date picker.
Wire the below code on the start date datacard border colour.
Note: DataCardValue3(End Date), DataCardValue2(StartDate)
If (And (
Or (
Weekday (Self.SelectedDate) in [1,7],
Self.SelectedDate in 'US Holiday List'.HolidayDate,
Self.SelectedDate > DataCardValue3.SelectedDate
),
! IsBlank (DataCardValue2.SelectedDate),
! IsBlank(DataCardValue3.SelectedDate)),Red,Parent.BorderColor
)
- Likewise, use the below code for End Date picker datacard BorderColor property.
If (And (
Or (
Weekday (Self.SelectedDate) in [1,7],
Self.SelectedDate in 'US Holiday List'.HolidayDate,
Self.SelectedDate - For the below screenshot we add the Start Date >End Date so border show in red color.
- Now you can add the button code as per your requirement.
- As the below Screenshot the number of business day is 18 based on the StartDate and End Date.
