cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shrikantswnt
Frequent Visitor

Get user defined total hours between two dates

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

 

shrikantswnt_0-1661030185225.png

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

shrikantswnt_2-1661030851094.png

 

List 2 : HolidaysCompany : where company maintain company holidays.

 

shrikantswnt_4-1661031201839.png

 

 

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.

12 REPLIES 12
ArtjolaZ
Regular Visitor

 

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.

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...!🤗

Great!

Thanks a lot ArtjolaZ!

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (4,487)