Help need in IF / ELSE statement

[AgiGovind]

Hi Community,

i was trying to use IF ELSE for SLA date (Red, Amber, Green).

IF [SLA] = todate (datetimeadd (datetimetoday(),1, "days")) THEN "Green"
ELSEIF [SLA] = datetimetoday() THEN "Amber"
ELSEIF [SLA] < datetimetoday() THEN "Red"
ELSE "Others"
ENDIF

Example: if today is Friday (Amber) and my next SLA is Monday, Then Monday should fall under "Green"

But with above mentioned IF ELSE statement, Monday falls under "Others" (because it consider weekends)

Pls help, my logic should be : it should not consider weekends.  SLA dates are from Monday to Friday. 
Kindly advise how to modify the above statement.

Thank you! 

[dYoast]

The simplest thing to do would be to just check if SLA is greater than today.

    IF [SLA] > datetimetoday() THEN 'Green"

This would cover the cases of Friday, Saturday and Sunday.

Otherwise, you will have to check if today is Friday, Saturday or Sunday and adjust your datetimeadd accordingly.

[jrlindem]

I agree with @dYoast 

I was curious about the question and dYoast's response so I built a quick workflow to see it in action.  Here's what I did for illustrative purposes:

I think you should be able to adapt to add in your Service Level expectations from there.  Meaning, if your SLA is 5 days then you can compute the SLA_Date based on that from, say, the ticket_open_date and then compute that against today in the same way.

Hope that helps, -Jay

[Yoshiro_Fujimori]

Hi @AgiGovind ,

If you go one step further to consider national holidays as well as weekends, you may want to check this weekly challenge.

https://community.alteryx.com/t5/Weekly-Challenges/Challenge-252-Working-Days-Remaining/m-p/708644/h...

It aims to create a macro to calculate the number of business days between two dates.

You can apply the result (Positive, Zero and Negative) to the IF / ELSE statement.

[AgiGovind]

Thank you @jrlindem & @dYoast. for your comments. So the logic with SLA date i was looking for was T-1 (Red), T=1 (Amber), T+1 (Green). if any date greater than T+1 (White). Now this logic works fine for the SLA dates from Monday to Thursday. However, on Friday (T=1) and then  Monday considered as (greater than T+1/White) . but the actual formula should be Friday (T=1/Amber) and Monday (T+1/Green), and Tuesday (greater than T+1/White). So to achieve this we would need a additional line in my earlier IF ELSE statement to exclude the weekends.