Building a Business Budget App in Power Apps #1 (Business Days)
1-June-2022
Calculating business days
While making a budget app in Power apps for the company I work for, I needed to know if the current business day mas grater or equal to business day 6, and I could not find a formula like in excel.
Excel Sample
Calculating the number of business days in excel is simple. You can use NETWORKDAYS formula that looks something like this =NETWORKDAYS(start date, end date)
The formula in excel can also accept a list of holydays.
working formula found online
You can find a couple of options online, this is the one the worked for me
With(
{
// generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence(dte_EndDate.SelectedDate - dte_StartDate.SelectedDate + 1),
dte_StartDate.SelectedDate + Value - 1
)
},
If(
And(
IsBlank(dte_StartDate.SelectedDate),
IsBlank(dte_EndDate.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]
)
)
)
Code changes for my specific use
The first thing that change is the sequence days,
dte_EndDate.SelectedDate = today()
dte_StartDate.SelectedDate = Date(Year(Today()),Month(Today()),1) (this gets the first date of the month )
The second change made
was removing the if statement for the date selector
resulting code:
Set(varBusinesDays,
With(
{
// generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence(Today() - Date(Year(Today()),Month(Today()),1) + 1),
Date(Year(Today()),Month(Today()),1) + Value - 1
)
},
// include only dates Monday to Friday
CountIf(
varDateRange,
Weekday(Value) in [2, 3, 4, 5, 6]
)
));