Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-898894%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Count%20No.%20of%20Staff%20per%20hour%20given%20the%20Information%20Attached%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898894%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421903%22%20target%3D%22_blank%22%3E%40ChristianMercado%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20facilitate%20solutions%2C%20please%20attach%20your%20sample%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898912%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Count%20No.%20of%20Staff%20per%20hour%20given%20the%20Information%20Attached%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898912%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20sir%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898725%22%20slang%3D%22en-US%22%3EHow%20to%20Count%20No.%20of%20Staff%20per%20hour%20given%20the%20Information%20Attached%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898725%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day%2C%3C%2FP%3E%3CP%3ECan%20someone%20help%20me%20to%20make%20this%20work%20easy%2C%3C%2FP%3E%3CP%3EI%20just%20want%20to%20count%20the%20total%20number%20of%20staff%20per%20hour%20per%20day%20given%20the%20raw%20data%20below.%3C%2FP%3E%3CP%3Eplease%20see%20the%20desired%20output%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERAW%20DATA%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3Edate%3C%2FTD%3E%3CTD%3Edate%3C%2FTD%3E%3CTD%3Ename%3C%2FTD%3E%3CTD%3Etin1%3C%2FTD%3E%3CTD%3Etin2%3C%2FTD%3E%3CTD%3Etin3%3C%2FTD%3E%3CTD%3Etout1%3C%2FTD%3E%3CTD%3Etout2%3C%2FTD%3E%3CTD%3Etout3%3C%2FTD%3E%3CTD%3Etin4%3C%2FTD%3E%3CTD%3Etout4%3C%2FTD%3E%3CTD%3Ehourswork%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3ESunday%3C%2FTD%3E%3CTD%3EMARY%20JOY%20ALJAS%3C%2FTD%3E%3CTD%3E12%3A14%20PM%3C%2FTD%3E%3CTD%3E4%3A58%20PM%3C%2FTD%3E%3CTD%3E7%3A49%20PM%3C%2FTD%3E%3CTD%3E4%3A00%20PM%3C%2FTD%3E%3CTD%3E7%3A32%20PM%3C%2FTD%3E%3CTD%3E9%3A15%20PM%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E7.72%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3ESunday%3C%2FTD%3E%3CTD%3ESARAH%20GERONIMO%3C%2FTD%3E%3CTD%3E12%3A14%20PM%3C%2FTD%3E%3CTD%3E3%3A42%20PM%3C%2FTD%3E%3CTD%3E6%3A41%20PM%3C%2FTD%3E%3CTD%3E2%3A45%20PM%3C%2FTD%3E%3CTD%3E6%3A28%20PM%3C%2FTD%3E%3CTD%3E9%3A15%20PM%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E7.8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3ESunday%3C%2FTD%3E%3CTD%3ELORIEBERN%20B.%3C%2FTD%3E%3CTD%3E9%3A23%20AM%3C%2FTD%3E%3CTD%3E2%3A40%20PM%3C%2FTD%3E%3CTD%3E6%3A24%20PM%3C%2FTD%3E%3CTD%3E1%3A37%20PM%3C%2FTD%3E%3CTD%3E6%3A07%20PM%3C%2FTD%3E%3CTD%3E6%3A48%20PM%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E8.07%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3ESunday%3C%2FTD%3E%3CTD%3EROSSANN%20LADESMA%3C%2FTD%3E%3CTD%3E9%3A22%20AM%3C%2FTD%3E%3CTD%3E1%3A17%20PM%3C%2FTD%3E%3CTD%3E6%3A40%20PM%3C%2FTD%3E%3CTD%3E12%3A18%20PM%3C%2FTD%3E%3CTD%3E6%3A24%20PM%3C%2FTD%3E%3CTD%3E6%3A48%20PM%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E8.13%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3EMonday%3C%2FTD%3E%3CTD%3ELORIEBERN%20B.%3C%2FTD%3E%3CTD%3E12%3A12%20PM%3C%2FTD%3E%3CTD%3E4%3A18%20PM%3C%2FTD%3E%3CTD%3E8%3A02%20PM%3C%2FTD%3E%3CTD%3E3%3A14%20PM%3C%2FTD%3E%3CTD%3E7%3A46%20PM%3C%2FTD%3E%3CTD%3E9%3A15%20PM%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E7.67%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3EMonday%3C%2FTD%3E%3CTD%3ESARAH%20GERONIMO%3C%2FTD%3E%3CTD%3E9%3A42%20AM%3C%2FTD%3E%3CTD%3E1%3A47%20PM%3C%2FTD%3E%3CTD%3E6%3A27%20PM%3C%2FTD%3E%3CTD%3E12%3A48%20PM%3C%2FTD%3E%3CTD%3E6%3A12%20PM%3C%2FTD%3E%3CTD%3E6%3A50%20PM%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E7.87%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3EMonday%3C%2FTD%3E%3CTD%3EMARY%20JOY%20ALJAS%3C%2FTD%3E%3CTD%3E9%3A42%20AM%3C%2FTD%3E%3CTD%3E2%3A56%20PM%3C%2FTD%3E%3CTD%3E7%3A24%20PM%3C%2FTD%3E%3CTD%3E1%3A56%20PM%3C%2FTD%3E%3CTD%3E7%3A09%20PM%3C%2FTD%3E%3CTD%3E9%3A15%20PM%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E10.27%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDESIRED%20OUTPUT%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EDate%3C%2FTD%3E%3CTD%3ETIME%3C%2FTD%3E%3CTD%3EIN%3C%2FTD%3E%3CTD%3EOUT%3C%2FTD%3E%3CTD%3E%23%20OF%20STAFF%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3E8%3A00%3A00%20AM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3E9%3A00%3A00%20AM%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3E10%3A00%3A00%20AM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3E11%3A00%3A00%20AM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3E12%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3E1%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3E2%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3E3%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3E4%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3E5%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3E6%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3E7%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3E8%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3E9%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F1%2F2019%3C%2FTD%3E%3CTD%3E10%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3E8%3A00%3A00%20AM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3E9%3A00%3A00%20AM%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3E10%3A00%3A00%20AM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3E11%3A00%3A00%20AM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3E12%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3E1%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3E2%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3E3%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3E4%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3E5%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3E6%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3E7%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3E8%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3E9%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F2%2F2019%3C%2FTD%3E%3CTD%3E10%3A00%3A00%20PM%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%3C%2FP%3E%3CP%3EChristianMercado%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-898725%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EHow%20to%20Count%20No.%20of%20Staff%20per%20hour%20given%20the%20Information%20Attached%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
ChristianMercado
Occasional 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.

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies