Due date calculation deducting weekend and public holiday

(0) Share

Report

Posted on by sgrpdl

109

Hi there,

Has any one got the solution for "Due date calculation deducting weekend and public holiday".

I got the pieces from this site.

But the issues is, output is wrong.

Example: Mr. XYZ return date is 3rd of Nov 2021 and there is public holiday on 4th and 5th. 6th and 7th is (Sat/Sun- Holiday) Weekend.

If i need to calculate due date (After 10 business day) then output should be 19th of Nov 2021.

but the my output is 11/17/2021 (17th Nov 2021) - Need expert advice.

With({ Rdate:DateValue(DC_ReturnDate.SelectedDate),
daysToAdd: 10
},
DateAdd(Rdate, daysToAdd) +
RoundDown(daysToAdd/5, 0)*2 +
Switch(Weekday(Rdate, StartOfWeek.Monday),
5,If(Mod(daysToAdd, 5)>0,2,0),
4,If(Mod(daysToAdd, 5)>1,2,0),
3,If(Mod(daysToAdd, 5)>2,2,0),
2,If(Mod(daysToAdd, 5)>3,2,0),
1,If(Mod(daysToAdd, 5)>4,2,0)
) +
CountIf(Publicholiday, DateValue(Date) >= Rdate, DateValue(Date) <= DateAdd(Rdate, daysToAdd))
)

Note: Publicholiday is collection where I have list of public holiday.
Date is the Holiday Date Field.

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

WarrenBelz 156,763 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @sgrpdl ,

This is a data issue, not a coding item - take the Public Holidays that fall on weekends out of the list.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps
sgrpdl 109 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WarrenBelz .
Well i tried this but still the output is wrong
CASE 1
Example: Mr. XYZ return date is 3rd of Nov 2021 and there is public holiday on 4th and 5th. 6th and 7th is Weekend.
If i need to calculate due date (After 10 business day) then output should be 19th of Nov 2021.
but the my output is 11/17/2021 (17th Nov 2021) - (Wrong output)

CASE 2
Example: Mr. XYZ return date is 4th of Nov 2021 and there is public holiday on 4th and 5th. 6th and 7th is Weekend.
If i need to calculate due date (After 10 business day) then output should be 19th of Nov 2021.
which is fine (Correct Output).

CASE 3
Example: Mr. XYZ return date is 7th of Nov 2021 (Sunday) and 7th is Weekend.
If i need to calculate due date (After 10 business day) then output should be 19th of Nov 2021.
which is Not fine (Wrong Output, it gives 11/21/2021).
WarrenBelz 156,763 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@sgrpdl ,

Your core calculation must be wrong (think about it - if there are no weekend public holidays how can it be counting them). I have never seen your formula before though - your might have a read of this article.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps
sgrpdl 109 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz Well the issues is, if the return date is on Public holiday or Wkend (Sat/Sun) then output is wrong. Else it gives correct output.
Ya there is some cal mix-match.
WarrenBelz 156,763 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@sgrpdl ,

Did you look at the document I posted? It is a much simpler process than yours and should work.
Verified answer

sgrpdl 109 on at

Like
a
(0)

Report
Copy link

Link copied!

I solved the issues;
I got clue from here:
https://youtu.be/QVvhDe6puq0?t=486