Get user defined total hours between two dates

(0) Share

Report

Posted on by shrikantswnt

118

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.

Categories:

Power Apps Pro Dev & ISV

I have the same question (0)

All responses (12)

Answers (1)

Sort by

Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

please try this and let us know your feedback.
Concatenate(Text(DateDiff(DatePicker1.SelectedDate,DatePicker2.SelectedDate,Hours)),":",Text(DateDiff(DatePicker1.SelectedDate,DatePicker2.SelectedDate,Minutes)),":",Text(DateDiff(DatePicker1.SelectedDate,DatePicker2.SelectedDate,Seconds)))

Was this reply helpful? Yes No
shrikantswnt 118 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your reply.
Brother, I need to calculate hours from another list according to the user's selected date.
please check my code mentioned in the post, you will get a better idea of what I have to achieve.

I need something ForAll or Collection to compare each date between selected dates to get hours for them from another list and sum them to show the total hours.

I need to replace hard-coded 7 in my code which at last.

Was this reply helpful? Yes No
AnkushBelpande 2 on at

Like (0)

Report
Copy link

Link copied!

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.. 😇👍

Was this reply helpful? Yes No
shrikantswnt 118 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
shrikantswnt 118 on at

Like (0)

Report
Copy link

Link copied!

It is not yet working after spending daily hour on this. Any hint or update guys?

Was this reply helpful? Yes No
GTR95 25 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
shrikantswnt 118 on at

Like (0)

Report
Copy link

Link copied!

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 🙂

Was this reply helpful? Yes No
GTR95 25 on at

Like (0)

Report
Copy link

Link copied!

I get it now, you have transition between two months and different working hours. I will try to think about solution.

Was this reply helpful? Yes No
shrikantswnt 118 on at

Like (0)

Report
Copy link

Link copied!

Thanks buddy.

Was this reply helpful? Yes No
Verified answer

ArtjolaZ 18 on at

Like (5)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No