You’re offline. This is a read only version of the page.
106
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.
Great!
Thanks a lot ArtjolaZ!
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...!🤗
Share
Report
All responses (
Answers (
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 buddy.
Thanks GTR95 for replying.
Your last words are "multiply it with hours" which varies according to the date that's why I am not getting exact hours and facing an issue while retrieving it.
please let me know if you find something 🙂
Not sure if I understood correctly, but you could count rows of HolidaysCompany list between two selected dates by filtering list, so that it returns number of only those rows that are within the selected dates. Then you will know how many company holidays is there to exclude from working hours. As for excluding weekends, you can substract: WeekNum(To date) -WeekNum(From date), so you could know the diff and multiply it with hours.
It is not yet working after spending daily hour on this. Any hint or update guys?
Thanks brother,
But as HR is maintaining 3 rows then why HR will add 365 rows?
For sure the team won't agree and not the client as well.
Can we add ForALL or Switch loop in place of hardcoded 7? Because I did it using javascript in SP on-premise and it works but I don't know how to apply that in PowerApps
Bro,
If U need a quick solution, then try maintaining a list for all the 365 days.
You can easily do that using an excel sheet which can be later imported as a list in SPO.
List columns:
1. Date
2. Working Hours
3. Holiday (Yes / No) -- If needed, else the first 2 columns should be sufficient.
Then use this list to calculate the hours.
You can also use your Holidays list to update the hours in this list using a flow or may be try a lookup.
But if you cannot change the already implemented logic, SeasonWorking Hours + Holidays list then I'll get back to you soon with a solution using that.. 😇👍
#1
WarrenBelz
55
Most Valuable Professional
#2
mmbr1606
44
Super User 2025 Season 1
#3
Michael E. Gernaey
31
Super User 2025 Season 1
