Leave Request include week day and Saturday

(0) Share

Report

Posted on by mmchx

154

Hi All,

Based on

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

If I want to include my workday and Saturday, I just +2 on this formula is it correct?

+1 for count the StartDate.SelectedDate and another +1 for count the saturday.

RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7, 0) * 5 +
Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5) + 2

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

Expiscornovus 33,970 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @mmchx,

If I understand correctly you want to use a same date difference calculation but for 6 working days instead of 5 like in the example, correct?

Your working days will be weekdays from 2 to 7. So, the range will also be different. I think you will also need to adjust mod function to reflect the new number of working days (6 instead of 5).

Can you try this formula instead:

RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7, 0) * 6 + Mod(6 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 6)

Was this reply helpful? Yes No
mmchx 154 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes, that's correct. and if I want to count the start date, I need to +1 right?
example: start date: 15/11/2021 End date: 17/11/2021
formula: 2 days
expected: 3 days (15/11,16/11,17/11)

Was this reply helpful? Yes No
Expiscornovus 33,970 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @mmchx,

Yes, if you want to count the start day as well you could add the +1.

Was this reply helpful? Yes No
Verified answer

v-bofeng-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @mmchx :

Iterating over each date in the time range is the most accurate algorithm, I suggest you use this formula:

Sum( ForAll( Sequence(DateDiff(StartDate.SelectedDate,EndDate.SelectedDate,Days)+1,0,1), {Value:If(Weekday(DateAdd(StartDate.SelectedDate,Value,Days))<>1,1,0)} ), Value )

If the previous formula is used, if the start date is Sunday, the count may deviate by 1 day

Best Regards,

Bof

Was this reply helpful? Yes No
Expiscornovus 33,970 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-bofeng-msft,

Nice catch, I missed that Sunday start date day. That formula is very useful, thanks for sharing.

However, my assumption was that a person would never start a leave on a non working day 😉

Was this reply helpful? Yes No
mmchx 154 on at

Like
a
(0)

Report
Copy link

Link copied!

HI @v-bofeng-msft

could you please explain why calculate right this?

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

Like
a
(0)

Report
Copy link

Link copied!

Hi @mmchx :

Because this formula uses ForAll to judge all dates within the start date and end date once, the result is bound to be correct.

Best Regards,

Bof

Was this reply helpful? Yes No