cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
syhrh
Dual Super User
Dual 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

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

View solution in original post

6 REPLIES 6
Alex_10
Super User
Super User

@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
Dual Super User
Dual Super User

@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

@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
Dual Super User
Dual Super User

@Alex_10 It works, thank you!

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

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 ?

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (4,340)