Requirement: Powerapps calculate business days between Two dates
Steps calculation of business day between two days:
- Calculates the number of full weeks between the selected dates, and then multiplies it by 5 to get the total number of weekdays in those full weeks.
- Calculates the number of weekdays between the two selected dates that are not part of full week.
- The Powerapps weekday function returns a number from 1 to 7 representing the day of the week, where 1 is Sunday and 7 is Saturday.
- This formula subtracts the weekday of the first selected date from the weekday of the second selected date, adds 5, and then takes the remainder when divided by 5 to get the number of weekdays in the partial week.
- Counts the number of holidays between the selected dates.
- The CountIf function counts the number of items in a collection that meet a certain condition, colHolidays represents the name of collection.
- In this case, it counts the number of items in the Holidays collection where the holiday falls between the two selected dates.
- Finally the formula subtract the number of holidays from the total number of weekdays calculated in step1 and Step2 to get the total number of business days between the two selected dates, taking into account weekdays and holidays.
The DateDiff function in Powerapps used to calculate the difference between two dates in a specified unit of time, the unit of time to use for the calculation.
On Text property of label use the below formula:
RoundDown(DateDiff(DatePicker1.SelectedDate,DatePicker2.SelectedDate,TimeUnit.Days)/7,0)*5 +Mod(5+Weekday(DatePicker2.SelectedDate)Weekday(DatePicker1.SelectedDate),5)-CountIf(colHolidays,StartDate>=DatePicker1.SelectedDate,StartDate<DatePicker2.SelectedDate)Create a Collection as colHolidays and call a columns as start date and end date etc
ClearCollect(colHolidays, {Title:1, StartDate:15/10/2023, EndDate: 21/10/2023}, {Title:2, StartDate:30/10/2023, EndDate: 12/11/2023})