cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anonymous21
Super User
Super User

Calculate Half Day

Hi, I'm trying to calculate Half-day if the box is checked in the Total Days Applied.

anonymous21_0-1656312048840.png

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
Alex_10
Super User
Super User

@anonymous21 

 

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

View solution in original post

4 REPLIES 4
Alex_10
Super User
Super User

@anonymous21 

 

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

 

 

@Alex_10 

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. 

Alex_10
Super User
Super User

@anonymous21 

 

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

@Alex_10 It works, thank you!

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

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

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

<
Users online (3,739)