SOLVED
Home

excel logic

%3CLINGO-SUB%20id%3D%22lingo-sub-889446%22%20slang%3D%22en-US%22%3Eexcel%20logic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889446%22%20slang%3D%22en-US%22%3E%3CP%3EKINDLY%20HELP%20US%20FOR%20MAKE%20A%20FORMULA%20FOR%20TOTAL%20HOURS%20THAT%20FALL%20BETWEEN%20SPECIFIC%20TWO%20TIMES%3C%2FP%3E%3CP%3ECONDITION%20IS%20%22Night%20Hours%20COUNTED%20FROM%2020%3A00%20-%2004%3A00%20THEN%22%3C%2FP%3E%3CP%3EHOW%20TO%20CALCULATE%20NIGHT%20HOURS%20IF%20THERE%20IS%20AVAILABLE%20ONLY%20%22%20TIME%20IN%22%20AND%20%22TIME%20OUT%20%22%26nbsp%3B%3C%2FP%3E%3CP%3EEXAMPLE%20%3A%3C%2FP%3E%3CP%3ETIME%20IN%20%3D%2019%3A30%26nbsp%3B%3C%2FP%3E%3CP%3ETIME%20OUT%20%3D%2006%3A00%3C%2FP%3E%3CP%3ENIGHT%20HOURS%20%3D%20%3F%20(%20FORMULA%20)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-889446%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889538%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20logic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889538%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418182%22%20target%3D%22_blank%22%3E%40BRIJESH_KUMAR_KUSHWAHA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20207px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135171i5151DC50B911F72F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20in%20C1%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMOD(B1-A1%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889697%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20logic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889697%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%3BTHERE%20IS%20CONDITION%20THAT%20NIGHT%20HOURS%20IS%20TREATED%20FROM%2020%3A00%20TO%2004%3A00%20HOW%20TO%20CALCULATION%20NIGHT%20HOURS%20BETWEEN%20TIME%20IN%20AND%20TIME%20OUT%20DATA%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889711%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20logic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889711%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%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EIF%20NIGHT%20IS%20COUNTED%20FROM%2022%3A00%20-%2006%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ES%20N%3C%2FTD%3E%3CTD%3ETIME%20IN%3C%2FTD%3E%3CTD%3ETIME%20OUT%3C%2FTD%3E%3CTD%3ETOTAL%20HOURS%3C%2FTD%3E%3CTD%3ETOTAL%20NIGHT%20HRS%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E10%3A00%3C%2FTD%3E%3CTD%3E21%3A00%3C%2FTD%3E%3CTD%3E13%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E15%3A00%3C%2FTD%3E%3CTD%3E03%3A00%3C%2FTD%3E%3CTD%3E12%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E17%3A00%3C%2FTD%3E%3CTD%3E06%3A00%3C%2FTD%3E%3CTD%3E11%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E19%3A30%3C%2FTD%3E%3CTD%3E05%3A30%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E21%3A30%3C%2FTD%3E%3CTD%3E08%3A45%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E03%3A00%3C%2FTD%3E%3CTD%3E15%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889713%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20logic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889713%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418182%22%20target%3D%22_blank%22%3E%40BRIJESH_KUMAR_KUSHWAHA%3C%2FA%3E%26nbsp%3B%20Can%20you%20please%20find%20the%20Caps%20Lock%20key%20on%20your%20keyboard%20and%20turn%20it%20off%3F%20Writing%20in%20all%20upper%20case%20on%20the%20internet%20is%20perceived%20as%20SHOUTING%26nbsp%3B%20and%20considered%20impolite.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889856%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20logic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889856%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418182%22%20target%3D%22_blank%22%3E%40BRIJESH_KUMAR_KUSHWAHA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20450px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135200i00FB61D77138F8B5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ein%20F3%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(%0A%20%20%20%24C3%26lt%3B%24D3%2C%0A%20%20%20%24B%241-MIN(%24B%241%2C%24C3)%2BMAX(%24A%241%2C%24D3)-%24A%241%2C%0A%20%20%20MOD(%20MIN(%24D3%2C%24B%241)-MAX(%24C3%2C%24A%241)%2C1)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889866%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20logic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889866%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%2C%20thank%20you%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418182%22%20target%3D%22_blank%22%3E%40BRIJESH_KUMAR_KUSHWAHA%3C%2FA%3E%26nbsp%3B%2C%20please%20check%20TechComm%20Code%20of%20Conduct%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FCommunity-Guidelines%2FMicrosoft-Tech-Community-Code-of-Conduct%2Fm-p%2F31390%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FCommunity-Guidelines%2FMicrosoft-Tech-Community-Code-of-Conduct%2Fm-p%2F31390%3C%2FA%3E%2C%20DON'T%20section%20(Don't%3A%26nbsp%3B%3CSPAN%3EUse%20crazy%20fonts%2C%20profanity%20or%20ALL%20CAPS.%20All%20caps%20and%20strange%20fonts%20are%20annoying.)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-891252%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20logic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-891252%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Sir%20for%20solving%20our%20problem%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%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-891256%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20logic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-891256%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%20for%20CAP%20Lock%20letter%20my%20English%20is%20very%20weak%3C%2FP%3E%3CP%3Eand%20also%20thanks%20for%20solving%20my%20problem%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%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-892212%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20logic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-892212%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418182%22%20target%3D%22_blank%22%3E%40BRIJESH_KUMAR_KUSHWAHA%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
BRIJESH_KUMAR_KUSHWAHA
Occasional Contributor

KINDLY HELP US FOR MAKE A FORMULA FOR TOTAL HOURS THAT FALL BETWEEN SPECIFIC TWO TIMES

CONDITION IS "Night Hours COUNTED FROM 20:00 - 04:00 THEN"

HOW TO CALCULATE NIGHT HOURS IF THERE IS AVAILABLE ONLY " TIME IN" AND "TIME OUT " 

EXAMPLE :

TIME IN = 19:30 

TIME OUT = 06:00

NIGHT HOURS = ? ( FORMULA )

9 Replies

@BRIJESH_KUMAR_KUSHWAHA 

For such sample

image.png

formula in C1 is

=MOD(B1-A1,1)

@Sergei Baklan THERE IS CONDITION THAT NIGHT HOURS IS TREATED FROM 20:00 TO 04:00 HOW TO CALCULATION NIGHT HOURS BETWEEN TIME IN AND TIME OUT DATA 

 

@Sergei Baklan 

IF NIGHT IS COUNTED FROM 22:00 - 06:00 
     
S NTIME INTIME OUTTOTAL HOURSTOTAL NIGHT HRS
110:0021:0013:00 
215:0003:0012:00 
317:0006:0011:00 
419:3005:30  
521:3008:45  
603:0015:00  
     

@BRIJESH_KUMAR_KUSHWAHA  Can you please find the Caps Lock key on your keyboard and turn it off? Writing in all upper case on the internet is perceived as SHOUTING  and considered impolite.

Solution

@BRIJESH_KUMAR_KUSHWAHA 

For such sample

image.png

in F3

=IF(
   $C3<$D3,
   $B$1-MIN($B$1,$C3)+MAX($A$1,$D3)-$A$1,
   MOD( MIN($D3,$B$1)-MAX($C3,$A$1),1)
)

@Ingeborg Hawighorst , thank you

@BRIJESH_KUMAR_KUSHWAHA , please check TechComm Code of Conduct https://techcommunity.microsoft.com/t5/Community-Guidelines/Microsoft-Tech-Community-Code-of-Conduct..., DON'T section (Don't: Use crazy fonts, profanity or ALL CAPS. All caps and strange fonts are annoying.)

Thanks Sir for solving our problem @Sergei Baklan 

Sorry for CAP Lock letter my English is very weak

and also thanks for solving my problem @Sergei Baklan 

@BRIJESH_KUMAR_KUSHWAHA , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies