Nested IF Function Regarding Dates

[RLandon217]

Hi All,

=IF(IF(WEEKDAY(J2)=7,J2+4+IF(WEEKDAY(J2+4)=7,2,IF(WEEKDAY(J2+4)=1,2,0)),IF(WEEKDAY(J2)=1,J2+3+IF(WEEKDAY(J2+4)=7,2,IF(WEEKDAY(J2+4)=1,2,0)),J2+2+IF(WEEKDAY(J2+2)=7,2,IF(WEEKDAY(J2+2)=1,2,0))))<L2,IF(WEEKDAY(J2)=7,J2+4+IF(WEEKDAY(J2+4)=7,2,IF(WEEKDAY(J2+4)=1,2,0)),IF(WEEKDAY(J2)=1,J2+3+IF(WEEKDAY(J2+4)=7,2,IF(WEEKDAY(J2+4)=1,2,0)),J2+2+IF(WEEKDAY(J2+2)=7,2,IF(WEEKDAY(J2+2)=1,2,0)))),IF(WEEKDAY(M2-(L2-J2))=7,M2-(L2-J2)+4+IF(WEEKDAY(M2-(L2-J2)+4)=7,2,IF(WEEKDAY(M2-(L2-J2)+4)=1,2,0)),IF(WEEKDAY(M2-(L2-J2))=1,M2-(L2-J2)+3+IF(WEEKDAY(M2-(L2-J2)+4)=7,2,IF(WEEKDAY(M2-(L2-J2)+4)=1,2,0)),M2-(L2-J2)+2+IF(WEEKDAY(M2-(L2-J2)+2)=7,2,IF(WEEKDAY(M2-(L2-J2)+2)=1,2,0)))))

I am working on modifiying a formula to measure SLA within a ticketing system. We have Assigned Timestamp (J2), Pending Timestamp (L2) and In Progress Timestamp (M2). Basically, we want the SLA to formulate an SLA Date if we have a 1 day SLA. I have the following formula below. It is currently set to 2 days (48 hrs), but all I have to do is change it from 2 days to 1 (24 hrs). If you would like any additional information, feel free to reach out to me. *Note: The formula below worked for the 48hr SLA, I believe the days just need to be changed.

Thanks in Advance!!

 

[ukmikeb]

Hi

In your formulae against weekday 1 and weekday 7 you test for both :-

IF(WEEKDAY(J2+4)=7,2,IF(WEEKDAY(J2+4)=1,2,0)),

Is it not the case that the tests will always result in 0?

And could you not reduce the above to :-

IF(OR(WEEKDAY(J2+4)={1,7}),2,0),

And for the J2+2 bit to :-

IF(OR(WEEKDAY(J2+2)={1,7}),2,0)

which is more likely to produce the alternative result.

hth

 

[RLandon217]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Assigned Timestamp[/TD]
[TD]SLA Date[/TD]
[TD]Pending Timestamp[/TD]
[TD]Inprogress Timestamp[/TD]
[TD]Closed Timestamp
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="class: xl86, width: 138, align: right"]3/11/2013 20:05:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="class: xl89, width: 138, align: right"]3/27/13 11:19 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl88, width: 128, align: right"]3/12/2013 11:52:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 145"]
<tbody>[TR]
[TD="class: xl88, width: 145, align: right"]3/25/2013 15:06:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 151"]
<tbody>[TR]
[TD="class: xl88, width: 151, align: right"]3/25/2013 15:06:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=IF(IF(WEEKDAY(J2)=7,J2+4+IF(WEEKDAY(J2+4)=7,2,IF(WEEKDAY(J2+4)=1,2,0)),IF(WEEKDAY(J2)=1,J2+3+IF(WEEKDAY(J2+4)=7,2,IF(WEEKDAY(J2+4)=1,2,0)),J2+2+IF(WEEKDAY(J2+2)=7,2,IF(WEEKDAY(J2+2)=1,2,0))))<l2,if(weekday(j2)=7,j2+4+if(weekday(j2+4)=7,2,if(weekday(j2+4)=1,2,0)),if(weekday(j2)=1,j2+3+if(weekday(j2+4)=7,2,if(weekday(j2+4)=1,2,0)),j2+2+if(weekday(j2+2)=7,2,if(weekday(j2+2)=1,2,0)))),if(weekday(m2-(l2-j2))=7,m2-(l2-j2)+4+if(weekday(m2-(l2-j2)+4)=7,2,if(weekday(m2-(l2-j2)+4)=1,2,0)),if(weekday(m2-(l2-j2))=1,m2-(l2-j2)+3+if(weekday(m2-(l2-j2)+4)=7,2,if(weekday(m2-(l2-j2)+4)=1,2,0)),m2-(l2-j2)+2+if(weekday(m2-(l2-j2)+2)=7,2,if(weekday(m2-(l2-j2)+2)=1,2,0)))))< td=""></l2,if(weekday(j2)=7,j2+4+if(weekday(j2+4)=7,2,if(weekday(j2+4)=1,2,0)),if(weekday(j2)=1,j2+3+if(weekday(j2+4)=7,2,if(weekday(j2+4)=1,2,0)),j2+2+if(weekday(j2+2)=7,2,if(weekday(j2+2)=1,2,0)))),if(weekday(m2-(l2-j2))=7,m2-(l2-j2)+4+if(weekday(m2-(l2-j2)+4)=7,2,if(weekday(m2-(l2-j2)+4)=1,2,0)),if(weekday(m2-(l2-j2))=1,m2-(l2-j2)+3+if(weekday(m2-(l2-j2)+4)=7,2,if(weekday(m2-(l2-j2)+4)=1,2,0)),m2-(l2-j2)+2+if(weekday(m2-(l2-j2)+2)=7,2,if(weekday(m2-(l2-j2)+2)=1,2,0)))))<>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

For some reason it isn't allowing me to enter the entire formula... . The timestamps involved are included in the table (5x3). Thanks for the reply Mike. Does anyone know how or where to insert formulas on this site? This is actually my first thread post.

Thanks!

RG

 

[ukmikeb]

Hi

Yes, I thought that there was probably something missing.

To show the whole formula,
Copy the formula to the message window,
Click the Go Advanced button
In the new window
select the whole of your formula and click the # tag button to the right of the 2nd options line (The one with Bold option).

A belated Welcome to the MrExcel Forum.

 

[RLandon217]

<l2,if(weekday(j2)=7,j2+4+if(weekday(j2+4)=7,2,if(weekday(j2+4)=1,2,0)),if(weekday(j2)=1,j2+3+if(weekday(j2+4)=7,2,if(weekday(j2+4)=1,2,0)),j2+2+if(weekday(j2+2)=7,2,if(weekday(j2+2)=1,2,0)))),if(weekday(m2-(l2-j2))=7,m2-(l2-j2)+4+if(weekday(m2-(l2-j2)+4)=7,2,if(weekday(m2-(l2-j2)+4)=1,2,0)),if(weekday(m2-(l2-j2))=1,m2-(l2-j2)+3+if(weekday(m2-(l2-j2)+4)=7,2,if(weekday(m2-(l2-j2)+4)=1,2,0)),m2-(l2-j2)+2+if(weekday(m2-(l2-j2)+2)=7,2,if(weekday(m2-(l2-j2)+2)=1,2,0)))))< html=""></l2,if(weekday(j2)=7,j2+4+if(weekday(j2+4)=7,2,if(weekday(j2+4)=1,2,0)),if(weekday(j2)=1,j2+3+if(weekday(j2+4)=7,2,if(weekday(j2+4)=1,2,0)),j2+2+if(weekday(j2+2)=7,2,if(weekday(j2+2)=1,2,0)))),if(weekday(m2-(l2-j2))=7,m2-(l2-j2)+4+if(weekday(m2-(l2-j2)+4)=7,2,if(weekday(m2-(l2-j2)+4)=1,2,0)),if(weekday(m2-(l2-j2))=1,m2-(l2-j2)+3+if(weekday(m2-(l2-j2)+4)=7,2,if(weekday(m2-(l2-j2)+4)=1,2,0)),m2-(l2-j2)+2+if(weekday(m2-(l2-j2)+2)=7,2,if(weekday(m2-(l2-j2)+2)=1,2,0)))))<>=IF(IF(WEEKDAY(J2)=7,J2+4+IF(WEEKDAY(J2+4)=7,2,IF(WEEKDAY(J2+4)=1,2,0)),IF(WEEKDAY(J2)=1,J2+3+IF(WEEKDAY(J2+4)=7,2,IF(WEEKDAY(J2+4)=1,2,0)),J2+2+IF(WEEKDAY(J2+2)=7,2,IF(WEEKDAY(J2+2)=1,2,0))))<L2,IF(WEEKDAY(J2)=7,J2+4+IF(WEEKDAY(J2+4)=7,2,IF(WEEKDAY(J2+4)=1,2,0)),IF(WEEKDAY(J2)=1,J2+3+IF(WEEKDAY(J2+4)=7,2,IF(WEEKDAY(J2+4)=1,2,0)),J2+2+IF(WEEKDAY(J2+2)=7,2,IF(WEEKDAY(J2+2)=1,2,0)))),IF(WEEKDAY(M2-(L2-J2))=7,M2-(L2-J2)+4+IF(WEEKDAY(M2-(L2-J2)+4)=7,2,IF(WEEKDAY(M2-(L2-J2)+4)=1,2,0)),IF(WEEKDAY(M2-(L2-J2))=1,M2-(L2-J2)+3+IF(WEEKDAY(M2-(L2-J2)+4)=7,2,IF(WEEKDAY(M2-(L2-J2)+4)=1,2,0)),M2-(L2-J2)+2+IF(WEEKDAY(M2-(L2-J2)+2)=7,2,IF(WEEKDAY(M2-(L2-J2)+2)=1,2,0)))))

Ahh, thank you for your help! And the welcome!


Ross

 

[RLandon217]

<l2,if(weekday(j2)=7,j2+4+if(weekday(j2+4)=7,2,if(weekday(j2+4)=1,2,0)),if(weekday(j2)=1,j2+3+if(weekday(j2+4)=7,2,if(weekday(j2+4)=1,2,0)),j2+2+if(weekday(j2+2)=7,2,if(weekday(j2+2)=1,2,0)))),if(weekday(m2-(l2-j2))=7,m2-(l2-j2)+4+if(weekday(m2-(l2-j2)+4)=7,2,if(weekday(m2-(l2-j2)+4)=1,2,0)),if(weekday(m2-(l2-j2))=1,m2-(l2-j2)+3+if(weekday(m2-(l2-j2)+4)=7,2,if(weekday(m2-(l2-j2)+4)=1,2,0)),m2-(l2-j2)+2+if(weekday(m2-(l2-j2)+2)=7,2,if(weekday(m2-(l2-j2)+2)=1,2,0)))))[ code]<="" html=""></l2,if(weekday(j2)=7,j2+4+if(weekday(j2+4)=7,2,if(weekday(j2+4)=1,2,0)),if(weekday(j2)=1,j2+3+if(weekday(j2+4)=7,2,if(weekday(j2+4)=1,2,0)),j2+2+if(weekday(j2+2)=7,2,if(weekday(j2+2)=1,2,0)))),if(weekday(m2-(l2-j2))=7,m2-(l2-j2)+4+if(weekday(m2-(l2-j2)+4)=7,2,if(weekday(m2-(l2-j2)+4)=1,2,0)),if(weekday(m2-(l2-j2))=1,m2-(l2-j2)+3+if(weekday(m2-(l2-j2)+4)=7,2,if(weekday(m2-(l2-j2)+4)=1,2,0)),m2-(l2-j2)+2+if(weekday(m2-(l2-j2)+2)=7,2,if(weekday(m2-(l2-j2)+2)=1,2,0)))))[>It still seems to not be able to take the full formula.

 

[ukmikeb]

Ross

Can you post the right hand side of the formula on its own?

 

[RLandon217]

I tried to do that and no luck.. Also tried to tag the separate functions and don't know what it is. Is there an e-mail option on this site by any chance?

Ross

 

[ukmikeb]

Ross

Will this condensed formula

IF(AND(WEEKDAY(J2)>1,WEEKDAY(J2)<7),J2+2+IF(OR(WEEKDAY(J2+2)={1,7}),2,0),J2+3+(WEEKDAY(J2)=7)+IF(OR(WEEKDAY(J2+4)={1,7}),2,0))

have the same result as the first part of your formula?

By virtue that if you add 4 days to a date that has a WEEKDAY of 1 or 7 the final result has to be WEEKDAY 4 or 5 so the formula then becomes :-

IF(AND(WEEKDAY(J2)>1,WEEKDAY(J2)<7),J2+2+IF(OR(WEEKDAY(J2+2)={1,7}),2,0),J2+3+(WEEKDAY(J2)=7))

hth

 

[ukmikeb]

I tried to do that and no luck.. Also tried to tag the separate functions and don't know what it is. Is there an e-mail option on this site by any chance?

If the part of the formula you are trying to post has lots of less than and greater than signs the try putting a space before and after each of those signs.

Fyi, as this site functions through HTML it is looking for those signs to process each post - it is a known gremlin in the works!

Alternatively, post the cells with the formula using the utilities mentioned in my signature.