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
)
)
So total working hours should show 16 hours instead of 14 hours.
I am fighting from the last 2 days to resolve the issue.
Solved! Go to 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.
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!
User | Count |
---|---|
3 | |
2 | |
2 | |
1 | |
1 |