cancel
Showing results for
Did you mean:
Super User

## Calculate Half Day

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

``````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) )
)
)``````
4 REPLIES 4
Super User

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) )
)
)``````

Super User

"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.

Super User

``````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) )
)
)``````
Super User

@Alex_10 It works, thank you!

Announcements

#### Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!