Forum Discussion

ChristianMercado's avatar
ChristianMercado
Copper Contributor
Oct 08, 2019

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

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 

excel
Formulas and Functions
How to Count No. of Staff per hour given the Information Attached

Resources