How to Count No. of Staff per hour given the Information Attached

Copper Contributor

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

datedatenametin1tin2tin3tout1tout2tout3tin4tout4hourswork
9/1/2019SundayMARY JOY ALJAS12:14 PM4:58 PM7:49 PM4:00 PM7:32 PM9:15 PM  7.72
9/1/2019SundaySARAH GERONIMO12:14 PM3:42 PM6:41 PM2:45 PM6:28 PM9:15 PM  7.8
9/1/2019SundayLORIEBERN B.9:23 AM2:40 PM6:24 PM1:37 PM6:07 PM6:48 PM  8.07
9/1/2019SundayROSSANN LADESMA9:22 AM1:17 PM6:40 PM12:18 PM6:24 PM6:48 PM  8.13
9/2/2019MondayLORIEBERN B.12:12 PM4:18 PM8:02 PM3:14 PM7:46 PM9:15 PM  7.67
9/2/2019MondaySARAH GERONIMO9:42 AM1:47 PM6:27 PM12:48 PM6:12 PM6:50 PM  7.87
9/2/2019MondayMARY JOY ALJAS9:42 AM2:56 PM7:24 PM1:56 PM7:09 PM9:15 PM  10.27

 

DESIRED OUTPUT

DateTIMEINOUT# OF STAFF
9/1/20198:00:00 AM000
9/1/20199:00:00 AM202
9/1/201910:00:00 AM002
9/1/201911:00:00 AM002
9/1/201912:00:00 PM112
9/1/20191:00:00 PM112
9/1/20192:00:00 PM112
9/1/20193:00:00 PM103
9/1/20194:00:00 PM003
9/1/20195:00:00 PM003
9/1/20196:00:00 PM351
9/1/20197:00:00 PM001
9/1/20198:00:00 PM001
9/1/20199:00:00 PM010
9/1/201910:00:00 PM000
9/2/20198:00:00 AM000
9/2/20199:00:00 AM101
9/2/201910:00:00 AM001
9/2/201911:00:00 AM001
9/2/201912:00:00 PM111
9/2/20191:00:00 PM102
9/2/20192:00:00 PM002
9/2/20193:00:00 PM011
9/2/20194:00:00 PM102
9/2/20195:00:00 PM002
9/2/20196:00:00 PM121
9/2/20197:00:00 PM010
9/2/20198:00:00 PM101
9/2/20199:00:00 PM010
9/2/201910:00:00 PM000

 

thank you

ChristianMercado

 

@Twifoo 

2 Replies

@ChristianMercado 

To facilitate solutions, please attach your sample file.

@Twifoo 

here sir

 

thank you