SOLVED
Home

How to Count No. of Staff per hour

%3CLINGO-SUB%20id%3D%22lingo-sub-899069%22%20slang%3D%22en-US%22%3EHow%20to%20Count%20No.%20of%20Staff%20per%20hour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-899069%22%20slang%3D%22en-US%22%3E%3CP%3Eplease%20help%20me!%3C%2FP%3E%3CP%3E%26nbsp%3B%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%3Eplease%20see%20attached%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%3C%2FP%3E%3CP%3EChristian%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-899069%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-899595%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Count%20No.%20of%20Staff%20per%20hour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-899595%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%0A%3CP%3EI'm%20not%20sure%20I%20understood%20your%20logic%20correctly%20since%20results%20are%20bit%20different%2C%20formulas%20are%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EIN%0A%3DSUMPRODUCT((%24E%242%3A%24G%24106%26gt%3B%24Q2)*(%24E%242%3A%24G%24106%26lt%3B(%24Q2%2B1%2F24))*(%24A%242%3A%24A%24106%3D%24P2))%0A%0AOUT%0A%3DSUMPRODUCT((%24H%242%3A%24J%24106%26gt%3B%24Q2)*(%24H%242%3A%24J%24106%26lt%3B(%24Q2%2B1%2F24))*(%24A%242%3A%24A%24106%3D%24P2))%0A%0A%23%20OF%20STAFF%0A%3DSUMPRODUCT((%24H%242%3A%24J%24106%26gt%3B%24Q2)*(%24E%242%3A%24G%24106%26lt%3B(%24Q2%2B1%2F24))*(%24A%242%3A%24A%24106%3D%24P2))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900935%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Count%20No.%20of%20Staff%20per%20hour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900935%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Breally%20great%20help.%20thank%20you%20very%20much%20for%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-901549%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Count%20No.%20of%20Staff%20per%20hour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-901549%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%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-914509%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Count%20No.%20of%20Staff%20per%20hour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-914509%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20compute%20for%20the%20total%20hours%20worked%20per%20employee.%20I%20used%20basic%20formula%20Time%20out%20-%20Time%20in%20but%20I%20get%20negative%20results%20when%20there%20is%20a%20blank%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
ChristianMercado
Occasional Contributor

please help me!

 

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

 

please see attached file

 

thank you

Christian

 

@Twifoo 

7 Replies
Solution

@ChristianMercado 

I'm not sure I understood your logic correctly since results are bit different, formulas are

IN
=SUMPRODUCT(($E$2:$G$106>$Q2)*($E$2:$G$106<($Q2+1/24))*($A$2:$A$106=$P2))

OUT
=SUMPRODUCT(($H$2:$J$106>$Q2)*($H$2:$J$106<($Q2+1/24))*($A$2:$A$106=$P2))

# OF STAFF
=SUMPRODUCT(($H$2:$J$106>$Q2)*($E$2:$G$106<($Q2+1/24))*($A$2:$A$106=$P2))

@Sergei Baklan really great help. thank you very much for this.

@ChristianMercado , you are welcome

hi @Sergei Baklan,

 

I need your help.

 

I want to compute for the total hours worked per employee. I used basic formula Time out - Time in but I get negative results when there is a blank cell.

 

Thank you in advance.

 

 

@ChristianMercado 

Blank cells are considered as zero, or 00:00 as the time. How to handle the situation when you have, for example, 3 time-ins and only 2 time-outs (which means one of time-outs wasn't put into the system) depends on your business logic.

If simply ignore pairs for which time-in or time-out is blank, the formula could be

=SUMPRODUCT((J2:L2-G2:I2)*(G2:I2>0)*(J2:L2>0)*24)

Please check in column P attached.

@Sergei Baklan  

Great help sir.

 

thank you very much.

Christian

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 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