• 408K Members
• 8,003 Online
• 464K Conversations
SOLVED

excel logic

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

Re: excel logic

For such sample

formula in C1 is

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

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

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

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.

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

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

Re: excel logic

Thanks Sir for solving our problem @Sergei Baklan

Re: excel logic

Sorry for CAP Lock letter my English is very weak

and also thanks for solving my problem @Sergei Baklan

Re: excel logic

@BRIJESH_KUMAR_KUSHWAHA , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies