Elapsed time formula

[NTCMM]

I'm trying  to set up the proper formula for the following two statements your support is highly appreciated i attached excel file:- 

1-((check If cell G3 is contain  Weekend or Holiday or Annual Leave and  at the same time check if cell C3  contain time from 15:00  to 7:00 then  do B3-A3 )).

=IF(AND(G3="Weekend"),B3-A3, IF(AND(G3="Holiday"),B3-A3, IF(AND(G3="Annual Leave"), B3-A3, IF(AND(C3>="15:00"),B3-A3,"B3-C3"))))*24

2-((check If cell G3 is not contain  Weekend or Holiday or Annual Leave and  at the same time check if cell C3  is not contain time from 15:00  to 7:00 then  do B3-C3 )).

[#2 · Accepted solution]

@NTCMM 

Perhaps

=IF(((G3="Weekend")+(G3="Annual Leave")+(G3="Holiday"))*(MOD(C3,1)>=7/24)*(MOD(C3,1)<=15/24),B3-A3,B3-C3)

[#3]

Many thanks Sergei can you explain it for me @Sergei Baklan

[#4]

i appreciated your replay as my statement
((check If cell G3 is contain Weekend or Holiday or Annual Leave and at the same time check if cell C3 contain time from 15:00 to 7:00 then do B3-A3 )).

in your formula i got for the following dates which the incident time was after or equal 15 should be the formula work in this case as =(B6-A6)*24 (time of escalation - working time) the result 2.9 and by your formula i got 19.4
Working Time Time of Escalation Time of incident
4/20/2020 7:30 4/20/2020 10:24:13 AM 4/19/2020 15:00:00


7<=T>=15 in this time (time of escalation - working time)*24
If the time of incident (T) as the following
15-16-17-18-19-20-21-22-23-00-01-02-03-04-05-06-07
or
3pm-4pm-5pm-6pm-7pm-8pm-9pm-10pm-11pm-12am-1am-2am-3am-4am-5am-6am-7am
Or G3 is contain
Weekend or Holiday or Annual Leave

[#5]

@NTCMM 

As I understood the logic of your first post

if cell G3 is contain  Weekend or Holiday or Annual Leave
then
   if cell C3  contain time from 15:00:01  to 6:59:59
   then B3-A3
   else <not defined: holiday but working time>
else (cell G3 is NOT contain  Weekend or Holiday or Annual Leave)
   if cell C3  contain time from 7:00  to 15:00
   then B3-C3
   else <not defined: working day but NOT working time>

In the sample (row 6) incident happened on Sunday (perhaps Weekend, but there is no Weekend mentioned) and was escalated on next working day. Perhaps logic shall be different or in G6 it shall be Weekend

(as well as in first row).

[#6]

@Sergei Baklan 

Yes brother
As your understood the logic is correct
weekend Sat & Fri