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
Highlighted
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
Highlighted

@BRIJESH_KUMAR_KUSHWAHA 

For such sample

image.png

formula in C1 is

=MOD(B1-A1,1)
Highlighted

@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 

 

Highlighted

@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  
     
Highlighted

@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.

Highlighted
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)
)
Highlighted

@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.)

Highlighted

Thanks Sir for solving our problem @Sergei Baklan 

Highlighted

Sorry for CAP Lock letter my English is very weak

and also thanks for solving my problem @Sergei Baklan 

Highlighted

@BRIJESH_KUMAR_KUSHWAHA , you are welcome

Related Conversations
Referencing/adding last item in multiple groups
cdolan27 in Excel on
0 Replies
Averaging hourly data in excel
EthanBala in Excel on
0 Replies
Conditional Formatting Help!
mgiglab in Excel on
0 Replies
listing items in ranked order
willowbe in Excel on
0 Replies
Remove duplicates not working properly
Mcelli in Excel on
1 Replies