Due Date and Time Calculation excluding weekends and Holidays

(0) Share

Report

Posted on by Emma Etigu

Hey Guys,

I need assistance calculating the due date and time. My scenario is as follows.

I have developed a Help desk system in power apps and would like to find the due date for each ticket created. My SLA is in hours. For example, a ticket with High priority has got an SLA of 4 hours. Our working time is between 8 am to 5 pm and we work Monday to Friday.

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

MVP-Phipps 3,508 Super User 2024 Season 1 on at

Like (0)

Report

I would use a Switch Statement to cover High, Medium, and Low priority. Then have a read of this article to help understand how to exclude wekends: https://powerapps.microsoft.com/de-at/blog/excluding-weekends-and-holidays-in-date-differences-in-powerapps/

Please Accept as Solution if it solves your question. Or just give it a Thumbs Up if it is helpful as can help others.
LinkedIn: https://www.linkedin.com/in/charlie-phipps-%F0%9F%91%A8%E2%80%8D%F0%9F%92%BB-91338715b/
YouTube: https://www.youtube.com/channel/UChmFBGU1YKIU91sNMQ7buGg
Twitter: https://twitter.com/phipps0218

Was this reply helpful? Yes No
Emma Etigu 26 on at

Like (0)

Report

Hi @phipps0218 ,
Thanks for your feedback. The solution you have suggested works best if I have a start date and an end date. What I am looking for is a scenario where I can use a DateAdd function to calculate the Due date and time on the ticket creation page.

Thanks

Was this reply helpful? Yes No
Verified answer

v-bofeng-msft on at

Like (2)

Report
Hi @eetigu ,

I've made a test for your reference:

I assume:

StartDateTime is DateTimeValue("2022-7-20T10:00:00")

There is a Holiday List ["2022-07-20","2022-07-21"]

"High" SLA 4 Hours

"Mid" SLA 8 Hours

"Low" SLA 16 Hours

The formula should be

With( {StartDateTime:DateTimeValue("2022-7-20T10:00:00"),Priority:"High",HolidayList:["2022-07-20","2022-07-21"]}, Last(FirstN( Sort( Filter( ForAll( Sequence(300), With( {TheEndDateTime:DateAdd(StartDateTime,Value,Hours)}, { EndDateTime:TheEndDateTime, Count:If( Weekday(TheEndDateTime) in [2,3,4,5,6] && Hour(TheEndDateTime) >=8 && Hour(TheEndDateTime) <=17 && !(Text(TheEndDateTime,"yyyy-mm-dd") in HolidayList),1,0) } ) ), Count=1), EndDateTime,Ascending), Switch(Priority,"High",4+1,"Mid",8+1,"Low",16+1))).EndDateTime )

Best Regards,

Bof

Was this reply helpful? Yes No
Emma Etigu 26 on at

Like (0)

Report

@v-bofeng-msft ,

The solution you provided worked.

Thanks a lot

Was this reply helpful? Yes No
legolass 2 on at

Like (0)

Report

Hi @eetigu
I know it has been a long time since you post, hope you see it. May i ask where did you write this formula in apps? I have been dealing with a similar issue, but i did not get how did you use this formula in apps.

With best regards

Was this reply helpful? Yes No