Hi, I'm trying to calculate Half-day if the box is checked in the Total Days Applied.
This is a Sharepoint form integrated with Power Apps. The Total Days Applied calculates the from the StartDate until EndDate, then I want if the user checks the Half-day box, it will calculate the half-day. For example, the StartDate is 6/27/2022 and the EndDate is 6/27/2022 so the Total Days Applied is 1, if the user checks the half-day box, the Total Days Applied will become 0.5 days. How to calculate that?
This code is the calculation for the Total Days Applied excluding weekends and public holidays.
With(
{
// generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence(DataCardValue17.SelectedDate - DataCardValue2.SelectedDate + 1),
DataCardValue2.SelectedDate + Value - 1
)
},
If(
And(
IsBlank(DataCardValue2.SelectedDate),
IsBlank(DataCardValue17.SelectedDate)
),
// show nothing if any date pickers are blank
0,
// show only dates Monday to Friday and exclude holidays
CountIf(
varDateRange,
And(
Weekday(Value) in [2, 3, 4, 5, 6],
Not(Value in Holidays.StartDate)
)
)
)
)
Thank you.
Solved! Go to Solution.
With({
// generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence(DataCardValue17.SelectedDate - DataCardValue2.SelectedDate + 1),
DataCardValue2.SelectedDate + Value - 1
)},
If(
IsBlank(DataCardValue2.SelectedDate) || IsBlank(DataCardValue17.SelectedDate),
0,
DataCardValue2.SelectedDate = DataCardValue17.SelectedDate && Toggle1.Value,
0.5,
DataCardValue2.SelectedDate = DataCardValue17.SelectedDate && !Toggle1.Value,
1,
!IsBlank(DataCardValue2.SelectedDate) && !IsBlank(DataCardValue17.SelectedDate) && Toggle1.Value
CountIf( varDateRange, Weekday(Value) in [2, 3, 4, 5, 6] && !(Value in Holidays.StartDate) ) / 2
CountIf( varDateRange, Weekday(Value) in [2, 3, 4, 5, 6] && !(Value in Holidays.StartDate) )
)
)
I assume that users cannot select half day when start date and end date are not the same and your formula works well except half day toggle on.
With({
// generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence(DataCardValue17.SelectedDate - DataCardValue2.SelectedDate + 1),
DataCardValue2.SelectedDate + Value - 1
)},
If(
IsBlank(DataCardValue2.SelectedDate) && IsBlank(DataCardValue17.SelectedDate), .
0,
DataCardValue2.SelectedDate = DataCardValue17.SelectedDate && Toggle1.Value,
0.5,
CountIf( varDateRange, Weekday(Value) in [2, 3, 4, 5, 6] && !(Value in Holidays.StartDate) )
)
)
Your calculation works!. Thank you.
"I assume that users cannot select half day when start date and end date are not the same"
- For this one, I want the calculation to apply for StartDate and EndDate as well. For example, the StartDate is 28/6/2022 and the EndDate is 30/6/2022 and the Half Day toggle is on so the Total Days Applied is 1.5 because there are 3 days the user applied for Half Day.
With({
// generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence(DataCardValue17.SelectedDate - DataCardValue2.SelectedDate + 1),
DataCardValue2.SelectedDate + Value - 1
)},
If(
IsBlank(DataCardValue2.SelectedDate) || IsBlank(DataCardValue17.SelectedDate),
0,
DataCardValue2.SelectedDate = DataCardValue17.SelectedDate && Toggle1.Value,
0.5,
DataCardValue2.SelectedDate = DataCardValue17.SelectedDate && !Toggle1.Value,
1,
!IsBlank(DataCardValue2.SelectedDate) && !IsBlank(DataCardValue17.SelectedDate) && Toggle1.Value
CountIf( varDateRange, Weekday(Value) in [2, 3, 4, 5, 6] && !(Value in Holidays.StartDate) ) / 2
CountIf( varDateRange, Weekday(Value) in [2, 3, 4, 5, 6] && !(Value in Holidays.StartDate) )
)
)
User | Count |
---|---|
152 | |
93 | |
84 | |
77 | |
58 |
User | Count |
---|---|
194 | |
174 | |
106 | |
96 | |
89 |