• 544K Members
• 5,270 Online
• 648K Conversations
SOLVED

## excel logic

Highlighted
Occasional Contributor

# 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 ONLY " TIME IN" AND "TIME OUT "

EXAMPLE :

TIME IN = 19:30

TIME OUT = 06:00

NIGHT HOURS = ? ( FORMULA )

9 Replies
Highlighted

# Re: excel logic

For such sample

formula in C1 is

``=MOD(B1-A1,1)``
Highlighted

# Re: excel logic

@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

# Re: excel logic

 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
Highlighted

# Re: excel logic

@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

# Re: excel logic

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

# Re: excel logic

@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

# Re: excel logic

Thanks Sir for solving our problem @Sergei Baklan

Highlighted

# Re: excel logic

Sorry for CAP Lock letter my English is very weak

and also thanks for solving my problem @Sergei Baklan

Highlighted

# Re: excel logic

@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