Solved: Find time (working hours) between dates excluding weekends and holidays.

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

Categories:

General Questions

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