Forum Discussion

NTCMM's avatar
NTCMM
Copper Contributor
Aug 07, 2020
Solved

Elapsed time formula

 

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

 

excel

5 Replies

      • NTCMM's avatar
        NTCMM
        Copper Contributor
        Aug 07, 2020
        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

Resources