Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ...
Hello @praveensai491 ,
Have you got the correct logic for this? I need the same.