• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Receiving and Processing cut off formula

R

rameshkumarsharma

New Member
Hi


Please help me to make a formula following the below conditions.

[pre]
Code: 
Wave 2	Country	        Receiving Cut off (CET)	Processing Cut off (CET)
L	Germany               4:45:00 PM	         5:00:00 PM
C	Germany               5:45:00 PM	         6:00:00 PM
L	Austria               4:00:00 PM	         4:30:00 PM
C	Austria               4:00:00 PM	         4:30:00 PM
L	Switzerland           4:30:00 PM	         4:45:00 PM
C	Switzerland           4:30:00 PM	         4:45:00 PM
[/pre]
Currently i am using very big formula. could you please suggest if we can have a short formula instead of below big formula.


=IF(Q3<>"",IF(Y3="Germany",IF(AJ3="L",IF(NETWORKDAYS(R3,Q3,$BF$3)<=1,IF(MOD(Q3,1)<=TIME(17,0,59),"Within SLA",IF(MOD(R3,1)>=TIME(16,44,59),"Within SLA","Outside SLA")),IF(NETWORKDAYS(R3,Q3,$BE$3)=2,IF(AND(MOD(R3,1)>TIME(16,44,59),MOD(Q3,1)<=TIME(17,0,59)),"Within SLA","Outside SLA"),"Outside SLA")),IF(OR(AJ3="C",AJ3="LC",AJ3="CL",AJ3="",AJ3=0,AJ3="O"),IF(NETWORKDAYS(R3,Q3,$BE$3)<=1,IF(MOD(Q3,1)<=TIME(17,59,59),"Within SLA",IF(MOD(R3,1)>=TIME(17,44,59),"Within SLA","Outside SLA")),IF(NETWORKDAYS(R3,Q3,$BE$3)=2,IF(AND(MOD(R3,1)>=TIME(17,29,59),MOD(Q3,1)<=TIME(17,45,59)),"Within SLA","Outside SLA"),"Outside SLA")))),IF(Y3="Switzerland",IF(AJ3="L",IF(NETWORKDAYS(R3,Q3,$BE$3:$BF$3)<=1,IF(MOD(Q3,1)<=TIME(16,45,59),"Within SLA",IF(MOD(R3,1)>=TIME(16,29,59),"Within SLA","Outside SLA")),IF(NETWORKDAYS(R3,Q3,$BE$3:$BF$3)=2,IF(AND(MOD(R3,1)>=TIME(16,29,59),MOD(Q3,1)<=TIME(16,45,59)),"Within SLA","Outside SLA"),"Outside SLA")),IF(OR(AJ3="C",AJ3="LC",AJ3="CL",AJ3="",AJ3=0,AJ3="O"),IF(NETWORKDAYS(R3,Q3,$BE$3:$BF$3)<=1,IF(MOD(Q3,1)<=TIME(16,45,59),"Within SLA",IF(MOD(R3,1)>=TIME(16,29,59),"Within SLA","Outside SLA")),IF(NETWORKDAYS(R3,Q3,$BE$3:$BF$3)=2,IF(AND(MOD(R3,1)>=TIME(16,29,59),MOD(Q3,1)<=TIME(16,45,59)),"Within SLA","Outside SLA"),"Outside SLA")),IF(Y3="Austria",IF(NETWORKDAYS(R3,Q3,$BE$3:$BF$3)<=1,IF(MOD(Q3,1)<=TIME(16,30,59),"Within SLA",IF(MOD(R3,1)>=TIME(15,59,59),"Within SLA","Outside SLA")),IF(NETWORKDAYS(R3,Q3,$BE$3:$BF$3)=2,IF(AND(MOD(R3,1)>=TIME(15,59,59),MOD(Q3,1)<=TIME(16,30,59)),"Within SLA","Outside SLA"),"Outside SLA")),"N/A"))))),"On Hold")


Regards,

Ramesh
 
Hi Ramesh ,


You cannot expect that others will try to understand this formula , and then try to suggest an improvement !


If you define the conditions which this formula is implementing , in plain English , then it will be easier to come up with a formula , which may be an improvement on your existing one.


Otherwise , you may have to wait a very long time !


Narayan
 
Ok. Narayan, Thanks for sugession.


Please find the below conditions:


Col(AJ)=L; Country(col Y)=Germany; Receiving Cut off in CET(col R)=4:45:00 pm; Processing Cut off in CET(col Q)= 5:00:00 PM (if any incident receive before receiving cut off time so that should be processed as per processing cut off then it's within SLA otherwise Outside SLA. +


Col(AJ)=C; Country(col Y)=Germany; Receiving Cut off in CET(col R)=5:45:00 pm; Processing Cut off in CET(col Q)= 6:00:00 PM (if any incident receive before receiving cut off time so that should be processed as per processing cut off then it's within SLA otherwise Outside SLA.


Regards,

Ramesh
 
Hi Ramesh ,


I think you have explained only a small part of the formula ; going through it , the relevant worksheet addresses are :


Q3 , R3 , Y3 , AJ3 , BE3:BF3


AJ3 can take on the values : "L" , "C" , "LC" , "CL" , 0 , "O" or blank


Y3 can take on the values Germany , Switzerland or Austria.


The output of the formula can be one of Within SLA , Outside SLA or N/A.


The basic construct is as follows :

[pre]
Code: 
If Q3 is blank Then
output = "On Hold"
Else
If Y3 = "Germany" Then
....
Else
If Y3 = "Switzerland" Then
....
Else
If Y3 = "Austria" Then
....
Else
....
EndIf
EndIf
EndIf
EndIf
[/pre]
The .... represent somewhat complicated logic , which will need to be explained or decoded.


Narayan
 
Yes. you are right.


AJ3 can take on the values : "L" , "C" , "LC" , "CL" , 0 , "O" or blank. It can't be blank. Also "L" will have seperate receiving & processing time and rest("C","LC","CL")for others.

REgards,

Ramesh
 
@RameshSharma

Hi!

Nick name shortened as requested here:

http://chandoo.org/forums/topic/multiple-search-in-a-cell#post-111633

Regards!
 
Hi Ramesh ,


I had mentioned in my earlier post :



The .... represent somewhat complicated logic , which will need to be explained or decoded.




Till now you have not given any explanation , which means that what I mentioned in my first post applies :



you may have to wait a very long time !




If you can understand how difficult and tedious it is to debug a formula without any data , you would not be so impatient ; can you make everyone's job easier by uploading your workbook with the data ? Then you might get a solution immediately.


Narayan
 
Hi Ramesh ,


Unfortunately , this forum does not have a provision to either upload files , or attach files to posts.


What you can do is to use your preferred public file-sharing website , such as RapidShare , SpeedyShare , ZippyShare , DropBox , GoogleDocs , upload your file using that website , give others permission to access and download your file , and then post that access link here in this same topic.


Use a public file-sharing website which does not require us to register at the website in order to download the file.


Narayan
 
You must log in or register to reply here.
Back
Top