SOLVED

excel logic

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

best response confirmed by BRIJESH_KUMAR_KUSHWAHA (Copper Contributor)
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

1 best response

Accepted Solutions
best response confirmed by BRIJESH_KUMAR_KUSHWAHA (Copper Contributor)
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)
)

View solution in original post