Aug 07 2020 09:59 AM
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 )).
Aug 07 2020 10:28 AM
SolutionPerhaps
=IF(((G3="Weekend")+(G3="Annual Leave")+(G3="Holiday"))*(MOD(C3,1)>=7/24)*(MOD(C3,1)<=15/24),B3-A3,B3-C3)
Aug 07 2020 09:27 PM
Aug 07 2020 10:01 PM
Aug 08 2020 08:51 AM
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).
Aug 09 2020 03:08 AM
Aug 07 2020 10:28 AM
SolutionPerhaps
=IF(((G3="Weekend")+(G3="Annual Leave")+(G3="Holiday"))*(MOD(C3,1)>=7/24)*(MOD(C3,1)<=15/24),B3-A3,B3-C3)