SOLVED

Checking if a time range falls between another time range

%3CLINGO-SUB%20id%3D%22lingo-sub-2769117%22%20slang%3D%22en-US%22%3EChecking%20if%20a%20time%20range%20falls%20between%20another%20time%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2769117%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20for%20a%20formula%20to%20help%20me%20check%20if%20a%20time%20range%20falls%20between%20another%20time%20range%2C%20please%20see%20example%20table%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22551%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22128px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2298px%22%3ED%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3EE%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3EF%3C%2FTD%3E%3CTD%20width%3D%2271px%22%3EG%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22128px%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3ETime%20From%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3ETime%20To%3C%2FTD%3E%3CTD%20width%3D%2298px%22%3EEmployee%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3ETime%20From%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3ETime%20To%3C%2FTD%3E%3CTD%20width%3D%2271px%22%3EPresent%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22128px%22%3E9%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3E19%3A00%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E%26nbsp%3B01%3A15%3C%2FTD%3E%3CTD%20width%3D%2298px%22%3EJohn%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3E8%3A00%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3E9%3A00%3C%2FTD%3E%3CTD%20width%3D%2271px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22128px%22%3E9%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3E19%3A00%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E%26nbsp%3B01%3A15%3C%2FTD%3E%3CTD%20width%3D%2298px%22%3EMary%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3E13%3A50%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3E15%3A30%3C%2FTD%3E%3CTD%20width%3D%2271px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22128px%22%3E10%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3E20%3A00%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E%26nbsp%3B00%3A30%3C%2FTD%3E%3CTD%20width%3D%2298px%22%3EDavid%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3E10%3A20%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3E11%3A00%3C%2FTD%3E%3CTD%20width%3D%2271px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20example%2C%20I'd%20like%20G%20to%20return%20True%2FFalse%20based%20on%20if%20E%20and%20F%20falls%20between%20B%20and%20C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2769117%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%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2769209%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20if%20a%20time%20range%20falls%20between%20another%20time%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2769209%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1161674%22%20target%3D%22_blank%22%3E%40AaronL89%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20about%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DAND(IF(E2%26gt%3B0.5%2CE2-1%2CE2)%26gt%3B%3DIF(B2%26gt%3B0.5%2CB2-1%2CB2)%2CIF(F2%26gt%3B0.5%2CF2-1%2CF2)%26lt%3B%3DIF(C2%26gt%3B0.5%2CC2-1%2CC2))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Time%20Comparison.jpg%22%20style%3D%22width%3A%20988px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311703i692DA9C4D9D37BBB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Time%20Comparison.jpg%22%20alt%3D%22Time%20Comparison.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2769294%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20if%20a%20time%20range%20falls%20between%20another%20time%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2769294%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECorrected%20the%20previous%20error%2C%20now%20encountering%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22512%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E11%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E7%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E14%3A00%3C%2FTD%3E%3CTD%20width%3D%2278%22%3ESunny%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E8%3A55%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E10%3A15%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AaronL89_0-1632194578024.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311735i5C7C56CAD4414773%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22AaronL89_0-1632194578024.png%22%20alt%3D%22AaronL89_0-1632194578024.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2771882%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20if%20a%20time%20range%20falls%20between%20another%20time%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2771882%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help.%3C%2FP%3E%3CP%3ERan%20the%20new%20formula%2C%20but%20getting%20false%20positives%20again%3F%20I've%20highlighted%20them%20in%20red%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20attached%20a%20sample%20set%20of%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22571%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E9%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E19%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E1%3A15%3C%2FTD%3E%3CTD%20width%3D%22137%22%3EHarry%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E8%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E8%3A40%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E9%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E19%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E1%3A15%3C%2FTD%3E%3CTD%20width%3D%22137%22%3EHarriett%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E13%3A50%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E15%3A30%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E10%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E20%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E0%3A30%3C%2FTD%3E%3CTD%20width%3D%22137%22%3ETony%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E10%3A20%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E11%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E11%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E19%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E0%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3EGarry%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E11%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E12%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CFONT%20color%3D%22%23000000%22%3EFALSE%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E11%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E18%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E23%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3EGagan%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E7%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E8%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CFONT%20color%3D%22%23FF0000%22%3ETRUE%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E11%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E7%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E14%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3ESunny%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E8%3A55%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E10%3A15%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E12%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E20%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E0%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3EMick%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E10%3A30%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E10%3A40%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E13%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E19%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E0%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3EN%2FA%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E14%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E9%3A30%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E19%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3ENicky%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E7%3A40%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E8%3A20%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CFONT%20color%3D%22%23FF0000%22%3ETRUE%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E14%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E11%3A30%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E20%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3EEthan%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E21%3A05%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E21%3A30%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CFONT%20color%3D%22%23FF0000%22%3ETRUE%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E14%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E11%3A30%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E20%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3EN%2FA%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E14%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E12%3A52%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E21%3A06%3C%2FTD%3E%3CTD%20width%3D%22137%22%3EEthan%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E21%3A05%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E21%3A30%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E15%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E9%3A30%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E16%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3ESAWINDER%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E18%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E19%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CFONT%20color%3D%22%23FF0000%22%3ETRUE%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E15%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E19%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E0%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3EBALRAJ%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E13%3A25%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E14%3A20%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E15%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E7%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E12%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3EBALRAJ%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E13%3A25%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E14%3A20%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CFONT%20color%3D%22%23FF0000%22%3ETRUE%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E15%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E11%3A30%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E19%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3ESUKHDEV%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E16%3A35%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E17%3A25%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E15%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E12%3A54%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E21%3A09%3C%2FTD%3E%3CTD%20width%3D%22137%22%3ESUKHDEV%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E16%3A35%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E17%3A25%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E16%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E7%3A30%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E16%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3ERODGER%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E15%3A25%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E15%3A45%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E16%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E15%3A30%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E20%3A30%3C%2FTD%3E%3CTD%20width%3D%22137%22%3ESUKHDEV%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E8%3A55%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E10%3A25%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CFONT%20color%3D%22%23FF0000%22%3ETRUE%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E17%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E7%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E12%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3EJohn%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E13%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E13%3A50%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CFONT%20color%3D%22%23FF0000%22%3ETRUE%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E17%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E8%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E16%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3EJohn%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E13%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E13%3A50%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E18%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E10%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E19%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3ESUKHDEV%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E7%3A10%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E8%3A10%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CFONT%20color%3D%22%23FF0000%22%3ETRUE%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22114%22%3E19%2F09%2F2021%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E9%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E17%3A00%3C%2FTD%3E%3CTD%20width%3D%22137%22%3ERAVINDER%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E9%3A30%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E10%3A00%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ETRUE%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AaronL89_0-1632255175930.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311878i845540B440539D8E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22AaronL89_0-1632255175930.png%22%20alt%3D%22AaronL89_0-1632255175930.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Good morning,

 

I'm looking for a formula to help me check if a time range falls between another time range, please see example table below:

 

ABCDEFG
DateTime FromTime ToEmployeeTime FromTime ToPresent?
9/09/202119:00 01:15John8:009:00 
9/09/202119:00 01:15Mary13:5015:30 
10/09/202120:00 00:30David10:2011:00 

 

In this example, I'd like G to return True/False based on if E and F falls between B and C

10 Replies

@AaronL89 

How about this?

 

=AND(IF(E2>0.5,E2-1,E2)>=IF(B2>0.5,B2-1,B2),IF(F2>0.5,F2-1,F2)<=IF(C2>0.5,C2-1,C2))

 

Time Comparison.jpg

 

@Subodh_Tiwari_sktneer 

 

Seems to have a Value Error:

AaronL89_0-1632192028663.png

 

Is it the time formatting?

 

@Subodh_Tiwari_sktneer 

 

Corrected the previous error, now encountering the following:

 

11/09/20217:0014:00Sunny8:5510:15FALSE

 

AaronL89_0-1632194578024.png

 

@AaronL89 

Try the following formula instead and see if that works for all the scenarios...

 

=AND(OR(MOD(E2,1)>B2,MOD(E2,1)<C2),OR(MOD(F2,1)>B2,MOD(F2,1)<C2))

 

Time Comparison.jpg

@Subodh_Tiwari_sktneer 

Thank you for your help.

Ran the new formula, but getting false positives again? I've highlighted them in red below.

 

Also attached a sample set of data.

 

9/09/202119:001:15Harry8:008:40FALSE
9/09/202119:001:15Harriett13:5015:30FALSE
10/09/202120:000:30Tony10:2011:00FALSE
11/09/202119:000:00Garry11:0012:00FALSE
11/09/202118:0023:00Gagan7:008:00TRUE
11/09/20217:0014:00Sunny8:5510:15TRUE
12/09/202120:000:00Mick10:3010:40FALSE
13/09/202119:000:00N/A  FALSE
14/09/20219:3019:00Nicky7:408:20TRUE
14/09/202111:3020:00Ethan21:0521:30TRUE
14/09/202111:3020:00N/A  TRUE
14/09/202112:5221:06Ethan21:0521:30TRUE
15/09/20219:3016:00SAWINDER18:0019:00TRUE
15/09/202119:000:00BALRAJ13:2514:20FALSE
15/09/20217:0012:00BALRAJ13:2514:20TRUE
15/09/202111:3019:00SUKHDEV16:3517:25TRUE
15/09/202112:5421:09SUKHDEV16:3517:25TRUE
16/09/20217:3016:00RODGER15:2515:45TRUE
16/09/202115:3020:30SUKHDEV8:5510:25TRUE
17/09/20217:0012:00John13:0013:50TRUE
17/09/20218:0016:00John13:0013:50TRUE
18/09/202110:0019:00SUKHDEV7:108:10TRUE
19/09/20219:0017:00RAVINDER9:3010:00TRUE

 

AaronL89_0-1632255175930.png

 

@AaronL89 

Time formulas are really tricky. Okay, how about this in G1?

=AND(MOD(E1,1)>=B1,MOD(F1,1)<=C1)
Still giving false positives with the time ranges provided.

I think the biggest issue with the times is that they are a range, but also could be over 2 days.
best response confirmed by AaronL89 (Occasional Contributor)
Solution

@AaronL89  I'll throw an option in.  Sorry it isn't 'pretty'. Also it uses LET() so if you don't have Excel 365 I will have to modify it for the older formulas:

 

=LET(a,B1,b,C1,c,E1,d,F1,bb,b+(b<a),dd,d+(d<c),AND(c<bb,dd>a))

 

EDIT:

actually the conversion is pretty simple:

=AND(E1<(C1+(C1<B1)),(F1+(F1<E1))>B1)

 

I do have 365, and it looks like the first formula is a winner, thanks very much.

I'm after function so, looks don't matter in this contest. Appreciate your help.