Calculating Hours with two date and time picker, excluding weekends and holidays and returning a sum of 8-hour days

(0) Share

Report

Posted on by vivekravi

241

Hello,

I am working on a vacation request app, where when a person chooses the start date and time, end date and time, it should return the total hours of vacation requested, assuming a 8-hour workday.

I also would like to exclude the weekends and company holidays in between the selected days .

For example, if I select vacation from 9/1/2023 7:00 AM (Friday) to 9/17/2023 3:00 PM (Sunday), then it should return 80 hours.

Basically it should calculate 8 hours each day for Sep 1, 5,6,7,8,11,12,13,14,15 and exclude Sep 4 (Company holiday) and weekends ( Sep 9,10,16,17).

Also the minutes dropdown has only 4 values (00,15,30,45) to restrict users to have only vacations in 15 min interval. So in the hours requested, the output should be like 3.50 (for 3 hours 30 minutes) or 4.25 (for 4 hours 15 minutes) or 6.75 (for 6 hours 45 minutes) or 2 hours (for 2 hours and 00 minutes)

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (2)

Sort by

Verified answer

v-bofeng-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @vivekravi ,

please try:

Set(StartDateTime,Date(2023,9,1)+Time(7,0,0)); Set(EndDateTime,Date(2023,9,17)+Time(15,0,0)); ClearCollect(Holiday,{Date:Date(2023,9,4)}); Sum( ForAll( Sequence(Round(DateDiff(StartDateTime,EndDateTime,TimeUnit.Minutes)/15,0),1,1), With( {timeTemp:DateAdd(StartDateTime,Value*15,TimeUnit.Minutes)}, If( Weekday(timeTemp)=1 || Weekday(timeTemp)=7 || Hour(timeTemp)<=9 || Hour(timeTemp)>17 || Hour(timeTemp)=13||!IsBlank(LookUp(Holiday,Text(Date,"yyyy-mm-dd")=Text(timeTemp,"yyyy-mm-dd"))), {Value:0}, {Value:0.25} ) ) ),Value)

Best Regards,
Bof

Was this reply helpful? Yes No
vivekravi 241 on at

Like (0)

Report
Copy link

Link copied!

Hello @v-bofeng-msft ,

Thank you for your response.

Will this start date and end date be dynamic? Meaning will it work for any date range or only for this particular date?

I want it to work for any selected date and time in the from date datepicker and the to date datepicker.

Thank you.

Was this reply helpful? Yes No
v-bofeng-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @vivekravi ,

Just replace the codes marked yellow with dynamic values.

Best Regards,
Bof

Was this reply helpful? Yes No
Verified answer

vivekravi 241 on at

Like (0)

Report
Copy link

Link copied!
Hello,

I modified to calculate the difference in 60 minutes instead of 15 minutes.

In the text property of 'Hours Requested', I used the following and it helped me to get the correct time difference, excluding holidays and weekends.

Sum( ForAll( Sequence( Round( DateDiff( DataCardValue3_1.SelectedDate + TimeValue(DDHour_2.Selected.Value & ":" & DDMinute_2.Selected.Value & " " & DDAMPM_2.Selected.Value), //Start Date and Time selected from the drop down of 'From Date' DataCardValue4_1.SelectedDate + TimeValue(DDHour_3.Selected.Value & ":" & DDMinute_3.Selected.Value & " " & DDAMPM_3.Selected.Value), //End Date and Time selected from the drop down of 'To Date' TimeUnit.Minutes ) / 60, 0 ), 1, 1 ), With( { timeTemp: Text(DateTimeValue(DateAdd( DataCardValue3_1.SelectedDate + TimeValue(DDHour_2.Selected.Value & ":" & DDMinute_2.Selected.Value & " " & DDAMPM_2.Selected.Value), //Start Date and Time selected from the drop down of 'From Date' Value*60, TimeUnit.Minutes )),DateTimeFormat.LongDateTime24) }, If( Weekday(timeTemp) = 1 || Weekday(timeTemp) = 7 || Hour(timeTemp) <= 7 || Hour(timeTemp) > 15 || !IsBlank( LookUp( colHoliday,DateValue(timeTemp) in colHoliday.Date //colHoliday is a collection based on a Sharepoint List which has 2 columns, 'Name of Holidays' and 'Date' ) ), {Value: 0}, {Value: 1} ) ) ), Value )

In the following example, it excludes the Thanks Giving, Black Friday, and the weekend (November 23, 24, 25 & 26) and gives the hours as 16

Similarly it excludes Sep 4 (Labor day) and weekends (Sep 2,3,9,& 10) and returns 40 hours

Thank you so much @v-bofeng-msft .

Was this reply helpful? Yes No