Excluding Non operational Hour's

[praveensai491]

Hi all,

i have Ticket reported date='12/12/2023 19:23:00' and Ticket resolved date=' 14/12/2023 10:33:00'

basically operation hours start from morning 6am to 23:59pm

if we calculate time difference between will get 35.33hr's between dates. I want to exclude non operational hours which is from 12:01 AM  to 5:59 AM hours need to exclude from total hours with respective dates

Example: from reported date 37min+4 hours=4:37hr's

13/12/2023 have 24hr's operational hours=18

14/12/2023 have 10:33hr- 6hrs non operational hrs(12:01 AM  to 5:59 AM)=4:33hrs

finally total operational hours b/w dates=26.7 hr's

In this case operational hours coming as i expected.

issue wt iam facing will be highlighted in yellow.

1st 4rows time coming correctly

5th row actual output will be: 9:30min

6th row actual output will be: 9:30min

7th row actual output will be: 10:30min

8th row actual output will be: 4:30min

5th row actual output will be: 4:12min

Thanks

Praveen

[marcus_sommer]

A quite simple approach would be to create an appropriate mapping-table which contained a timestamp on date + hour/minute level as lookup-value and a continuous number as return - maybe simply rowno(). It's quite similar to a calendar-generation and the 0 - 5 hours are excluded with a  where-clause.

It will result 394 k of records for one year and even by 10 years it's further practicably useable for a mapping. And then you grab the value with something:

interval(1/24/60 * (applymap('Map', EndDate) - applymap('Map', StartDate))) as MyField

Such approach avoids numerous if-loops and could also easily handle advanced requirements like breaks, weekends, holidays ...

[PrachiKulkarni]

Hello @praveensai491 ,

Have you got the correct logic for this? I need the same.