You’re offline. This is a read only version of the page.
Hi Guys,
I have a help desk system designed with power apps and I would like to calculate SLA time for each ticket. In order to achieve this, I must calculate the number of hours a ticket has spent to the end of its lifecycle.
The SLA not count weekends and public holidays. The time We work from 8am - 5pm. My data is stored on a sharepoint list.
How do I proceed?
Thanks
I assume there is a StartDateTime and an EndDateTime and a Holidylist:
Since there are 9 hours between 9 am and 6 pm, I think that the work hour of the day is 9.
You can use this formula to calculate work hours:
With(
{
varStartDateTime: DateTimeValue(varSelectedRecorddetails.InitialResponseOn),
varEndDateTime: If(varSelectedRecorddetails.ResolutionDate=Blank(),Now(),DateTimeValue(varSelectedRecorddetails.ResolutionDate)),
HolidayList: {Date:"2023/01/30"}
},
With(
{
varMinuts: If(DateDiff(varStartDateTime,varEndDateTime, TimeUnit.Minutes) > 0,
Sum(
ForAll(Sequence(DateDiff(varStartDateTime,varEndDateTime) + 1,1,1),
If(
Weekday(DateAdd(varStartDateTime,Value - 1,TimeUnit.Days)) in [1,7] ||
Text(DateAdd(varStartDateTime,Value - 1,TimeUnit.Days),"yyyy/mm/dd") in HolidayList.Date,
{Value: 0},
{
Value: If(
ThisRecord.Value = 1,
((If(
DateValue(varStartDateTime) = DateValue(varEndDateTime),
Hour(varEndDateTime) + (Minute(varEndDateTime) / 60),
18
)) - (Hour(varStartDateTime)+Minute(varStartDateTime) / 60)),
Value(ThisRecord.Value) = DateDiff(
varStartDateTime,
varEndDateTime,
TimeUnit.Days
) + 1,
((Hour(varEndDateTime) + (Minute(varEndDateTime) / 60)) - 9),
9
)
}
)
),
Value
) * 60,
0
)
},
RoundDown(varMinuts / 60,0) & " hr " & Mod(varMinuts,60) & " mins"
)
)
I think I solved this myself!
Share
Report
All responses (
Answers (
If(
DateDiff(
StartDateTime,
EndDateTime
) > 1,
Sum(
ForAll(
Sequence(
DateDiff(
StartDateTime,
EndDateTime
) - 1,
1,
1
),
If(
Weekday(
DateAdd(
StartDateTime,
Value,
Days
)
) in [
0,
7
] || Text(
DateAdd(
StartDateTime,
Value,
Days
),
"yyyy/mm/dd"
) in HolidayList.Date,
{Value: 0},
{Value: 10}
)
),
Value
) * 60,
0
) + If(
DateDiff(
StartDateTime,
EndDateTime,
Days
) = 0,
If(
Text(
StartDateTime,
"yyyy/mm/dd"
) in HolidayList.Date || Weekday(StartDateTime) in [
0,
7
],
0,
If(
Int(
Text(
StartDateTime,
"HH"
)
) >= 18,
0,
DateDiff(
StartDateTime,
EndDateTime,
Minutes
)
)
),
If(
Text(
StartDateTime,
"yyyy/mm/dd"
) in HolidayList.Date || Weekday(StartDateTime) in [
0,
7
],
0,
If(
Int(
Text(
StartDateTime,
"HH"
)
) >= 18,
0,
DateDiff(
StartDateTime,
DateTimeValue(
Concatenate(
Text(
StartDateTime,
"yyyy-mm-dd"
),
"T18:00:00"
)
),
Minutes
)
)
) + If(
Text(
EndDateTime,
"yyyy/mm/dd"
) in HolidayList.Date || Weekday(EndDateTime) in [
0,
7
],
0,
If(
Int(
Text(
EndDateTime,
"HH"
)
) <= 8,
0,
DateDiff(
DateTimeValue(
Concatenate(
Text(
EndDateTime,
"yyyy-mm-dd"
),
"T08:00:00"
)
),
EndDateTime,
Minutes
)
)
)
)Hi @v-bofeng-msft do you have any idea how to change your code to receive the exact amount of minutes? 🙂 Would be very helpful.
Hi @v-bofeng-msft ,
Thanks for your response. Your solution works really well. Much appreciated.
Thanks
Emma.
Hi @eetigu :
I assume there is a StartDateTime and an EndDateTime and a Holidylist:
Set(StartDateTime,DateTimeValue("2022/1/17 20:00:00"));
Set(EndDateTime,DateTimeValue("2022/1/21 7:00:00"));
ClearCollect(HolidayList,{Date:"2022/01/30"})Since there are 9 hours between 8am and 5pm, I assume that the work hour of the day is 9.
You can use this formula to calculate work hours:
If(
DateDiff(StartDateTime,EndDateTime)>1,
Sum(
ForAll(
Sequence(DateDiff(StartDateTime,EndDateTime)-1,1,1),
If(
Weekday(DateAdd(StartDateTime,Value,Days)) in [0,7] || Text(DateAdd(StartDateTime,Value,Days),"yyyy/mm/dd") in HolidayList.Date,
{Value:0},
{Value:9}
)
),
Value
),
0)+
If(
DateDiff(StartDateTime,EndDateTime,Days)=0,
If(
Text(StartDateTime,"yyyy/mm/dd") in HolidayList.Date || Weekday(StartDateTime) in [0,7],
0,
If(
Int(Text(StartDateTime,"HH"))>=17,
0,
9-Max(0,Text(StartDateTime,"HH")-8)-Max(0,17-Text(EndDateTime,"HH"))
)
),
If(
Text(StartDateTime,"yyyy/mm/dd") in HolidayList.Date || Weekday(StartDateTime) in [0,7],
0,
If(
Int(Text(StartDateTime,"HH"))>=17,
0,
Max(0,17-Text(StartDateTime,"HH"))
)
)+
If(
Text(EndDateTime,"yyyy/mm/dd") in HolidayList.Date || Weekday(EndDateTime) in [0,7],
0,
If(
Int(Text(EndDateTime,"HH"))<=8,
0,
Min(9,Text(EndDateTime,"HH")-8)
)
)
)
Best Regards,
Bof
Hi @eetigu ,
I assumed you would multiply the days by the number of hours worked in a day, minus the time left on the start day.
Hi Guys,
Any suggestions on how to go about this?
Thanks.
Hi @WarrenBelz ,
Thank you for your response. However the solution you have suggested works best for getting date difference while excluding weekends and holidays but not business hours. I am currently trying to factor in the hours and if I get a solution I will post it here. Thanks
Hi @eetigu ,
You might start with this document.
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
#1
WarrenBelz
791
Most Valuable Professional
#2
MS.Ragavendar
410
Super User 2025 Season 2
#3
mmbr1606
275
Super User 2025 Season 2
