Calculate Half Day

[syhrh]

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.

[Alex_10]

@syhrh 

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

[syhrh]

@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 · Accepted solution]

@syhrh 

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

[syhrh]

@Alex_10 It works, thank you!

[Avinash2]

Hello Sir,

Im getting error while using this code:

please advise how can i fix it

With({
// generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence(Enddate.SelectedDate - Startdate.SelectedDate + 1),
Startdate.SelectedDate + Value - 1
)
},

If(

IsBlank(Startdate.SelectedDate) || IsBlank(Enddate.SelectedDate),
0,

Startdate.SelectedDate = Enddate.SelectedDate && Toggle1.Value,
0.5,

Startdate.SelectedDate = DataCardValue17.SelectedDate && !Toggle1.Value,
1,

!IsBlank(Startdate.SelectedDate) && !IsBlank(Enddate.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) )
)
)

[Avinash2]

Hello Sir,

Today the formula worked. Great.

Sir if employee want leave from 20-09-2022 - 22-09-2022. Where if they need to take half day in all 3 days , how can i do that ?

[Avinash2]

Here in this formula , suppose end date = start date = 17/11/2022 [ if 17 is a holiday ]

the formula is returning 1 as a final value.

can you advise how we can solve it?