@ballardw may be i am not being clear what i want. have attached the data below let me know if this work Input data: this is employee logging into a in house system for adherence. some time the activity can be 2 hours long. Actual Start Actual End Nov 1, 2019 5:02:49 PM Nov 1, 2019 5:02:55 PM Nov 1, 2019 5:41:18 PM Nov 1, 2019 5:46:45 PM Nov 1, 2019 5:46:45 PM Nov 1, 2019 5:51:57 PM Nov 1, 2019 5:51:57 PM Nov 1, 2019 6:00:16 PM Nov 1, 2019 6:00:16 PM Nov 1, 2019 6:02:01 PM Nov 1, 2019 6:02:01 PM Nov 1, 2019 6:06:44 PM Nov 1, 2019 6:31:08 PM Nov 1, 2019 6:32:24 PM Nov 1, 2019 6:35:57 PM Nov 1, 2019 6:37:30 PM Nov 1, 2019 6:41:48 PM Nov 1, 2019 6:41:56 PM Nov 1, 2019 6:42:42 PM Nov 1, 2019 6:43:10 PM Nov 1, 2019 6:44:01 PM Nov 1, 2019 6:44:21 PM Nov 1, 2019 6:47:52 PM Nov 1, 2019 6:48:00 PM Nov 1, 2019 8:26:36 PM Nov 1, 2019 8:26:45 PM Nov 1, 2019 8:37:05 PM Nov 1, 2019 8:39:12 PM Nov 1, 2019 8:43:21 PM Nov 1, 2019 8:44:15 PM Nov 1, 2019 9:35:37 PM Nov 1, 2019 9:35:51 PM required output : Shift time is employee's shift and i want this to be a divided into hour groups starting from 00 - 24 (which will include business hours for the company) Productive time: this is the time difference or the actual time the employee is logged into the system from the input data. Date Shift-time (4pm-12am) Productive Time 1-Nov-19 16:00-17:00 0 min 1-Nov-19 17:00-18:00 7 min 1-Nov-19 18:00-19:00 10 min 1-Nov-19 19:00-20:00 0 min 1-Nov-19 20:00-21:00 3 min 1-Nov-19 21:00-22:00 1 min 1-Nov-19 22:00-23:00 0 min 1-Nov-19 23:00-00:00 0 min the reason why i want the hour interval to be 4-5 format is, i have another data set(files worked in each hour) that i will be joining to this which has hour interval as 4-5. Hope this clarifies the doubt. please let me know if you have any questions.
... View more