Forum Discussion
Solved
excel logic
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 ...
For such sample
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) )
| IF NIGHT IS COUNTED FROM 22:00 - 06:00 | ||||
| S N | TIME IN | TIME OUT | TOTAL HOURS | TOTAL NIGHT HRS |
| 1 | 10:00 | 21:00 | 13:00 | |
| 2 | 15:00 | 03:00 | 12:00 | |
| 3 | 17:00 | 06:00 | 11:00 | |
| 4 | 19:30 | 05:30 | ||
| 5 | 21:30 | 08:45 | ||
| 6 | 03:00 | 15:00 | ||
For such sample
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)
)Thanks Sir for solving our problem SergeiBaklan
BRIJESH_KUMAR_KUSHWAHA , you are welcome