How to Count No. of Staff per hour given the Information Attached
Good day, Can someone help me to make this work easy, I just want to count the total number of staff per hour per day given the raw data below. please see the desired output below. RAW DATA date date name tin1 tin2 tin3 tout1 tout2 tout3 tin4 tout4 hourswork 9/1/2019 Sunday MARY JOY ALJAS 12:14 PM 4:58 PM 7:49 PM 4:00 PM 7:32 PM 9:15 PM 7.72 9/1/2019 Sunday SARAH GERONIMO 12:14 PM 3:42 PM 6:41 PM 2:45 PM 6:28 PM 9:15 PM 7.8 9/1/2019 Sunday LORIEBERN B. 9:23 AM 2:40 PM 6:24 PM 1:37 PM 6:07 PM 6:48 PM 8.07 9/1/2019 Sunday ROSSANN LADESMA 9:22 AM 1:17 PM 6:40 PM 12:18 PM 6:24 PM 6:48 PM 8.13 9/2/2019 Monday LORIEBERN B. 12:12 PM 4:18 PM 8:02 PM 3:14 PM 7:46 PM 9:15 PM 7.67 9/2/2019 Monday SARAH GERONIMO 9:42 AM 1:47 PM 6:27 PM 12:48 PM 6:12 PM 6:50 PM 7.87 9/2/2019 Monday MARY JOY ALJAS 9:42 AM 2:56 PM 7:24 PM 1:56 PM 7:09 PM 9:15 PM 10.27 DESIRED OUTPUT Date TIME IN OUT # OF STAFF 9/1/2019 8:00:00 AM 0 0 0 9/1/2019 9:00:00 AM 2 0 2 9/1/2019 10:00:00 AM 0 0 2 9/1/2019 11:00:00 AM 0 0 2 9/1/2019 12:00:00 PM 1 1 2 9/1/2019 1:00:00 PM 1 1 2 9/1/2019 2:00:00 PM 1 1 2 9/1/2019 3:00:00 PM 1 0 3 9/1/2019 4:00:00 PM 0 0 3 9/1/2019 5:00:00 PM 0 0 3 9/1/2019 6:00:00 PM 3 5 1 9/1/2019 7:00:00 PM 0 0 1 9/1/2019 8:00:00 PM 0 0 1 9/1/2019 9:00:00 PM 0 1 0 9/1/2019 10:00:00 PM 0 0 0 9/2/2019 8:00:00 AM 0 0 0 9/2/2019 9:00:00 AM 1 0 1 9/2/2019 10:00:00 AM 0 0 1 9/2/2019 11:00:00 AM 0 0 1 9/2/2019 12:00:00 PM 1 1 1 9/2/2019 1:00:00 PM 1 0 2 9/2/2019 2:00:00 PM 0 0 2 9/2/2019 3:00:00 PM 0 1 1 9/2/2019 4:00:00 PM 1 0 2 9/2/2019 5:00:00 PM 0 0 2 9/2/2019 6:00:00 PM 1 2 1 9/2/2019 7:00:00 PM 0 1 0 9/2/2019 8:00:00 PM 1 0 1 9/2/2019 9:00:00 PM 0 1 0 9/2/2019 10:00:00 PM 0 0 0 thank you ChristianMercado Twifoo