Help need in IF / ELSE statement

Question

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"
ENDIFExample: 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!

OllieClarke · Answer

Hi @AgiGovind

I think this is the simplest way is to make an updated [Today] field with this formula:

//check if today is Friday or Saturday
If datetimeformat(DateTimeToday(),'%u') IN('5','6')

//If so then shift it to Sunday
THEN datetimeadd(DateTimeToday(), 7-tonumber(datetimeformat(DateTimeToday(),'%u')),'day')

//Otherwise keep it as is
ELSE DateTimeToday()
ENDIF

You can then use your original formula except use this field instead of DateTimeToday() in your green clause

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

Hope that helps,

Ollie

jrlindem · Answer

@AgiGovind Ah, gotcha.  Yes, this makes more sense for real world applications. Here's a revised workflow and logic to account for the behavior I believe you're after.  I did not include Holidays as @Yoshiro_Fujimori  recommended, but you can adapt from here.

Use the Simulation Date text input tool to put in either today or another date you want to treat as if it was "today".  The other dates in the SLA Date Examples are just a span of dates to show the leading/lagging dates to check the logic.  You may need to update those if you choose a Simulation Date that isn't within that span.  I hope that makes sense 😅

Also attached for reference,

Hope that helps illustrate how you can use logic to get to where you need to be.  It's done long-form, to make it easier to show what I'm doing logically.

-Jay

SLA_Logic_Example_v2.yxmd

AgiGovind · Answer

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.