Subtracting holidays and weekends in leave request won't work

(0) Share

Report

Posted on by kharina

249

Dear PowerApps,

I am using this formula to subtract the weekend and holidays in my leave form app:

***RoundDown(DateDiff(DatePicker1_6.SelectedDate, DatePicker1_7.SelectedDate, Days) / 7, 0) * 5 +

Mod(5 + Weekday(DatePicker1_7.SelectedDate) - Weekday(DatePicker1_6.SelectedDate), 5)

-CountIf(Holidays, StartDate >= DatePicker1_6.SelectedDate, StartDate <= DatePicker1_7.SelectedDate)****

And it doesn't work. If I add " +1" at the end of my formula, it will work for a leave submission of 1 week only. But if I try to submit a date range of more than 7 days, it will not work anymore.

Can you please help me correct my formula?

Thank You in advance,

Kharina

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (2)

v-jefferni on at

Like (1)

Report

Hi @kharina ,

Do you want to count actual leave days from subtracting holidays and weekends in a leave request?

If so, I think the problem in your formula is coming from the last part. What is the "Holiday" table? Is this table holding holiday names and dates in each row?

I checked this blog and tested on my side which worked fine, so I assuming the problem in your App comes from the "Holiday" table. Please notice that all holidays in the blog only stand for a single day.

Or if possible please share more details about your Holiday table.

Hope this helps.

Best regards,
Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
kharina 249 on at

Like (0)

Report

Hello @v-jefferni ,

I appreciate your reply.
Thank you also for the link. I will read it too.

I use these: (at the OnStart of the App)

ClearCollect(Holidays,
{HolidayName: "Christmas", StartDate: DateValue("12/25/2018")},
{HolidayName: "DayAfterChristmas", StartDate: DateValue("12/26/2018")},
{HolidayName: "New Year's Day", StartDate: DateValue("1/1/2019")})

I used those few holidays for now- just to test my app. I don't know how to deal with the weekdays. Sorry

Thank You and Kind Regards,
Kharina

Was this reply helpful? Yes No
v-jefferni on at

Like (1)

Report

Hi @kharina ,

The formula you are using has already resolved the weekends and holidays if you have fulfilled the holiday table. Noticed that holidays in your table are with dates in 2018 and 2019, so they would never be counted and excluded as you are selecting dates in 2020.

Please complete your holiday table with years of 2020 and 2021 to test more accurately.

Hope this helps.

Best regards,
Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
kharina 249 on at

Like (0)

Report

Hello @v-jefferni ,

I thought that the dates are actually the start of calendar selection that will go on- until the next year and so on.....

😞 I did the test already. my formula works for 1 week leave request. but if try to file 2 weeks, instead of 4 weekend days, it subtracts 7 weekend days 😞

Can you help me what else I can check?

Thank You & Kind Regards,
Kharina

Was this reply helpful? Yes No
v-jefferni on at

Like (1)

Report

Hi @kharina ,

Could you please made a test that remove the CountIf part of the formula? I made a test and it reduced weekends correctly, no matter how long is the leaving duration.

Please test it and provide the result, and better provide your new holiday table as well.
Waiting for your reply.

Best regards,
Community Support Team _ Jeffer Ni

Was this reply helpful? Yes No
kharina 249 on at

Like (0)

Report

Hello @v-jefferni ,

I tried removing the CountIf portion but I can only get the right outputs for 1 week leave application:

But if I try a few days with 2 weekends, the formula won't work anymore.

Another thing that I discovered is the holidays.
I tried to include the christmas day but - it won't deduct Dec. 25 as a holiday:
For this screenshot above, I'm getting 3 days of leave instead of 2. 😞

Is there anywhere in my app that I have to double check again?

Thank You very much,
Kharina

Was this reply helpful? Yes No
Verified answer

v-jefferni on at

Like (1)

Report
Hi @kharina ,

I made a test with a Calendar of US holidays:

The formula of the Label:
RoundDown(DateDiff(DatePicker1.SelectedDate, DatePicker2.SelectedDate, Days) / 7, 0) * 5 +Mod(5 + Weekday(DatePicker2.SelectedDate+1) - Weekday(DatePicker1.SelectedDate), 5)-CountIf(BrowseGallery1.AllItems, DateValue(start) >= DatePicker1.SelectedDate, DateValue(start) <= DatePicker2.SelectedDate)

You could see those holidays in my test that each day occupies one row, which your collection need to be the same as. If you have collected all holidays you need, same format as your above post, your formula would be:
RoundDown(DateDiff(DatePicker1.SelectedDate, DatePicker2.SelectedDate, Days) / 7, 0) * 5 +Mod(5 + Weekday(DatePicker2.SelectedDate+1) - Weekday(DatePicker1.SelectedDate), 5)-CountIf(Holidays, StartDate >= DatePicker1.SelectedDate, StartDate <= DatePicker2.SelectedDate)

If you would like to make a test for Christmas day and New Year Eve, try below collection:
ClearCollect(Holidays, {HolidayName: "Christmas Eve", StartDate: DateValue("12/24/2020")}, {HolidayName: "Christmas", StartDate: DateValue("12/25/2020")}, {HolidayName: "DayAfterChristmas", StartDate: DateValue("12/26/2020")}, {HolidayName: "New Year's Day", StartDate: DateValue("1/1/2021")} )

Hope this helps.

Best regards,
Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
kharina 249 on at

Like (0)

Report

Hi @v-jefferni ,

I really appreciate your help and patience.
I believe that your solution really works because @v-silky-msft clicked the "accept as soution" already on this thread.
I more than appreciate that you still replied to me.
I separated the holidays and weekends so that I can see where the issue is coming from- but I still couldn't find it.
The formula you gave me works but not for 2 things:
1. If a holiday falls on a weekend, instead of 1 day, it subtracts 2 days.
2. I tried to record my screen - to show you this: whenever the end date falls on the 29th of December ( for the 2nd picker), I am getting a negative value:

I showed here multiple date selections - it's just the 29th of December that I'm getting the negative value.

Do you have any idea what wrong I did on this?

Thank you and kind regards,
Kharina

Was this reply helpful? Yes No
Verified answer

v-jefferni on at

Like (1)

Report
Hi @kharina ,

Don't worry, I have found the issue that if holidays are in weekends, the subtracted dates are repeated, also I made a mistake that "+1" wrong placed and that's why a negative value came up.

I corrected the formula, add judgements that holidays in weekends would not be counted in:
RoundDown(DateDiff(StartDatePicker.SelectedDate,EndDatePicker.SelectedDate, Days) / 7, 0) * 5 + Mod(5 + Weekday(EndDatePicker.SelectedDate) - Weekday(StartDatePicker.SelectedDate), 5) +1- CountIf(Holidays, StartDate >= StartDatePicker.SelectedDate, StartDate <= EndDatePicker.SelectedDate,Not( Weekday(StartDate)=7),Not(Weekday(StartDate)=1))

Please have a try.

Hope this helps.

Best regards,
Community Support Team _ Jeffer Ni

Was this reply helpful? Yes No
kharina 249 on at

Like (0)

Report

Dear @v-jefferni ,

I'm so lucky that you did not give up on me. I really want to thank you. I've been dealing with this issue for weeks. I really tried a lot of ways but I've never seen an example with a "Not function" from samples and videos that I found.

Really... Thank you very very much. I admire your patience.

By the way, seeing your "Don't worry" from your last reply really made my day with a big smile on my face.
I Salute you!

Cheers <3,
Kharina

Was this reply helpful? Yes No