Using IF Statement on Date-Times Columns to calculate based on Three conditions

[Anonymous]

I want to use an IF statement to create three conditions based on appointments for bookings.  

I have three columns of times. Created Datetime, Failture datetime and Succeed Date Time.

The calculation must be relative and take account of the actual date of today.

The three conditions...

1. If succeed datetime is blank and the failure datetime is prior todays date (22/01/2020) then the condition is "Fail"

2. if the failture date is ahead of todays date and the succeed datetime is blank then condtion is "Inside SLA"

3.  IF the succeed column has a date that is before the failture column then "Pass"

All of these work with the current function

SLA = IF(SLA[Faildate] < today() || SLA[Faildate] < SLA[Succeed], "Fail", IF(ISBLANK(SLA[Succeed]&&(SLA[Fail]>Today())), "Inside" SLA","Pass"))

The issue is that the equation doesnt take into account these Cases. These should be passes because the succeed date is eariler than the fail date. 

Anyway around this?

[JustJan]

Hi @Anonymous ,

I think you have an issue with the brackets on the bold part of the statement. Now you are testing if the combination of fields isblank, but you shoul only test if succeed is blank (at least that is what I thoug was the idea.

Jan 

[Anonymous]

Hi  JustJan,

Yes added the bracket in for this part 

IF(ISBLANK(slakpiinstances[succeededon])&&(slakpiinstances[failuretime]>TODAY()),"Inside SLA",

and it has worked.

Thank you

[JustJan · Accepted solution]

Hi @Anonymous ,

I think the problem is in the first part of your if statement. Where you say; IF(SLA[Faildate] < today() || SLA[Faildate] < SLA[Succeed], "Fail". You use an 'or' statement here, so if the the faildate < today (which is the case for you last 4 lines) or faildate<succeed then the SLA status is Fail. 

The Columns SLA2 is your IF statement. SLA3 is showing the expected result (if I am not mistaken)

SLA3 looks like : 

SLA3 = 
switch(
 true(),
 ISBLANK(SLA[Succeed]) && SLA[FailDate] <TODAY(),"Fail",
 ISBLANK(SLA[Succeed]) && SLA[FailDate] >= TODAY(), "Inside SLA",
 SLA[Succeed]<=SLA[FailDate], "Pass",
 "")

 I think it is easier to read and I hope it helps.

jan

if this is a solution for you, don't forget to mark it as such. thanks

Jan