Answered

Custom working days and hours

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hey all,

Can you please give me an advice about this:

In this POST @Sienna managed to find me an amazing way how to add hours to a date or even days, while skipping Friday and Saturday, and 15:30 till 07:59.

Now what I kindly want, and hopefully can someone figure it:

How can I make custom working hours, like if the working hours changed from 08:00 - 15:30 to 09:30 - 14:30.
How can I add Holidays that I input to be skipped from the calculations done by Sienna in the post I linked.

For example. To have TextInput1 and TextInput2, TextInput1=start of working hours (09:30), TextInput2=end of working hours (14:30)

And another example for holidays. Put holidays on OneDrive and put it as a source in PowerApps and to read that these days the formula has to bypass them as if they were Friday and Saturday which are weekends.

Thanks again.

It would be appreciated if someone could help me with this.

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

Verified answer

v-micsh-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
For deal with hoilday, please check:

https://powerapps.microsoft.com/en-us/blog/excluding-weekends-and-holidays-in-date-differences-in-powerapps/

Basically the logic here is to count the number of the hoildays, then remove the number from the DateDiff() calculation.

For example, the table to record the holiday is named Holiday2017:

RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7, 0) * 5 + Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5) - CountIf(Holidays2017, Date >= StartDate.SelectedDate, Date <= EndDate.SelectedDate)

For hour calculation, if here you would like to use Sienna's example, the part you need to change is 480 and 450.

480 is the start working time, which should be replaced by

60*value(StartHour.text)+value(StartMinute.text)

450 is the working duration, which should be replaced by:

DateDiff(Time(StartHour.text,StartMinute.Text),time(EndHour.Text,EndMinute.Text),Minutes)

Regards,

Michael
tianaranjo 497 on at

Like
a
(0)

Report
Copy link

Link copied!
@v-micsh-msft

I'd like to add days from the date an item is created to assign it as a due date. I want to exclude weekends and holidays when assigning the due date. I created a datasource with the company holidays. Currently DueDate is DateAdd(Now(), 2).

Can you tell me how to modify the formula? This is working as intended -- if I have a start and end date. However, I want to populate a date based only on one date, Now().
Thank you!

RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7, 0) * 5 + Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5) - CountIf(Holidays2017, Date >= StartDate.SelectedDate, Date <= EndDate.SelectedDate)
tianaranjo 497 on at

Like
a
(0)

Report
Copy link

Link copied!

Looking to throw this out there again - I'd like to AddDays to a date of Now(), taking into consideration weekends and holidays. Suggestions would be appreciated.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@v-micsh-msft

Thank you appreciate your efforts 🙂
rajivmadhav 4 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @tianaranjo I am also looking for the same. Let me know if you have already found the answer.
SimplrMac 35 on at

Like
a
(0)

Report
Copy link

Link copied!

How about calculate time/minutes between 'ranges' of hours?

For example; 8:00/17:59 = DAY, so later I will calculate overtime for normal tariff
But 17:59/23:59=EVENING, NIGHT till 7.59 and for weekends another schedule.

The calculation of minutes is done.
DateDiff(DateTimeValue(CheckOut.Text); DateTimeValue(CheckIn.Text)) * 1440 + Minute( DateTimeValue(CheckOut.Text)) - Minute(DateTimeValue(CheckIn.Text))

But I want to know how many minutes in DAY and how many in EVENING?
So checkin 17.58 checkout 18.02 must give me
2 minutes DAY
2 minutes EVENING

Any ideas?
Thank you!