Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
Considering all the tickets are opened and closed between 7AM to 7PM which is 12 hours a day excluding weekends. I would like to get the below output using open date and closed date for every ticket.
I would like to generate the dates in between the open date and closed date for each ticket.
Raw data :
Incident | Open date | Closed date |
INC002 | 12/8/2022 12:45:00 PM | 12/13/2022 9:45:00 AM |
Required output: Column "New Dates" & "Total hours"
INC | New Dates | Total Hours | Reason |
INC001 | 12/8/2022 12:45:00 PM | 6:15 hours | ( 12:45PM to 7:00 PM (Business hour)) |
INC002 | 12/9/2022 | 12 hours | (Fullday- 7AM to 7PM business hours) |
INC003 | 12/10/2022 | 0 | Since weekends are not considered |
INC004 | 12/11/2022 | 0 | Since weekends are not considered |
INC005 | 12/12/2022 | 12 hours | (Fullday- 7AM to 7PM business hours) |
INC006 | 12/13/2022 9:45:00 AM | 2:45 hours | ( 7AM(Business hour) to 9:45AM) |
Any Help on this will be appreciated.
Thank you,
Hitha Dhani
without giving the business rules how to calculate the total hours, supplying sample data and the sample output would help others understand your problem.
i would be interested to see a sample incident that closes before the the close date of the requested range:
incidentx: opened 12/1/2022 closes 12/30/2022 but the user wants to see all incidents between 11/1/2022 and 12/15/2022. is this incident included? what if the incident opened before the requested date range but closed inside the date range or after the date range is it included?
also it is curious why your topic is "duplicate date" when there is no mention of duplicate dates in your question. appears to me theres a lot of info missing here
hi
see attached sample app
hope it helps