Oct 02 2019 10:57 AM
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 )
Oct 02 2019 11:22 AM
Oct 02 2019 12:58 PM
@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
Oct 02 2019 01:04 PM
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 | ||
Oct 02 2019 01:05 PM
@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.
Oct 02 2019 02:14 PM
SolutionFor 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)
)
Oct 02 2019 02:17 PM
@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.)
Oct 03 2019 06:58 AM
Sorry for CAP Lock letter my English is very weak
and also thanks for solving my problem @Sergei Baklan
Oct 02 2019 02:14 PM
SolutionFor 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)
)