Forum Discussion
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 Ann...
Perhaps
=IF(((G3="Weekend")+(G3="Annual Leave")+(G3="Holiday"))*(MOD(C3,1)>=7/24)*(MOD(C3,1)<=15/24),B3-A3,B3-C3)
Many thanks Sergei can you explain it for me SergeiBaklan
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
((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
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).