Get user defined total hours between two dates

[shrikantswnt]

Hi, I am new to power apps. I need help to resolve one tricky issue. details mentioned below:

List 1 : Season Working Hours : Working hours are different according to dates.

List 2 : HolidaysCompany : where company maintain company holidays.

I need calculate "No of Hours" for the days between "From Date" and "End Date" excluding weekends and "company holidays (Mentioned in the list)". Currently I have hard coded 7 so it is showing 14 value.

my code is below : 

With(
{
// generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence(DCV_ToDate.SelectedDate - DCV_FromDate.SelectedDate + 1),
DCV_FromDate.SelectedDate + Value - 1
)
},
If(
And(
IsBlank(DCV_FromDate.SelectedDate),
IsBlank(DCV_ToDate.SelectedDate)
),
// show nothing if any date pickers are blank
0,
// show only dates Sunday to Thursday and exclude holidays
CountIf(
varDateRange,
And(
Weekday(Value) in [1, 2, 3, 4, 5],
Not(Value in HolidaysCompany.Date)
)
)*7
)
)

• According to the  selected dates '7' hours for '31 Oct 2022'
• Skip the company holiday '1 Nov'
• '9' hours for '2 Nov'

So total working hours should show 16 hours instead of 14 hours.

I am fighting from the last 2 days to resolve the issue.

[ArtjolaZ · Accepted solution]

With(
    {
        // generate a one-column table of all dates between start date & end date
        varDateRange: ForAll(
            Sequence(DCV_ToDate.SelectedDate - DCV_FromDate.SelectedDate + 1),
            {
                Date: DCV_FromDate.SelectedDate + Value - 1,
                Hours: First(
                    Filter(
                        'Season Working hours',
                        'End Date' >= DateAdd(
                            DCV_FromDate.SelectedDate,
                            Value - 1,
                            Days
                        ),
                        'Start Date' <= DateAdd(
                            DCV_FromDate.SelectedDate,
                            Value,
                            Days
                        )
                    )
                ).Hours
            }
        )
    },
    If(
        And(
            IsBlank(DCV_FromDate.SelectedDate),
            IsBlank(DCV_ToDate.SelectedDate)
        ),
// show nothing if any date pickers are blank
        0,
// show only dates Sunday to Thursday and exclude holidays
        Sum(
            varDateRange,
            If(
                And(
                    Weekday(Date) in [
                        2,
                        3,
                        4,
                        5,
                        6
                    ],
                    Not(Date in 'Holidays Companies'.date)
                ),
                Hours,
                0
            )
        )
    )
)

I have found this solution, but I have changed the weekday array to match my time zone.

[shrikantswnt]

Thanks ArtjolaZ, I have implemented you solution by doing some changes as per my requirement and it works 😍. This is exactly what I was looking for.
Thanks a lot...!🤗

[radhe_mali]

Great!

Thanks a lot ArtjolaZ!