Blank Dates reading as Pass

[CRogers22]

Hello:

I am running a formula between four dates which is a >=30 and it seems if the date is blank its reading it as less than 30.

IF
ToNumber([Initial 30 Days Commitment])<=30
OR
ToNumber([Initial 30 Days NOI])<=30
OR
ToNumber([Initial 30 Days Denial])<=30
OR
ToNumber([Initial 30 Days Withdrawn])<=30
THEN 'Pass'
ELSE 'Fail'
ENDIF

How do I write it to make it fail if its fails the first test and 2nd 3rd and 4th test return (Blank) as there is missing a date. Also if none have the number of days it should be a Fail.

[alexnajm]

Add a portion to the beginning of your IF statement to capture those issues!

[alexnajm]

IF

IsNull([Initial 30 Days Commitment])
OR
IsNull([Initial 30 Days NOI])
OR
IsNull([Initial 30 Days Denial])
OR
IsNull([Initial 30 Days Withdrawn])

THEN null()

ELSEIF
ToNumber([Initial 30 Days Commitment])<=30
OR
ToNumber([Initial 30 Days NOI])<=30
OR
ToNumber([Initial 30 Days Denial])<=30
OR
ToNumber([Initial 30 Days Withdrawn])<=30
THEN 'Pass'
ELSE 'Fail'
ENDIF

[CRogers22]

Hey @alexnajm thank you. but that is removing the results because there will be a set of dates on each that will be Null. I need it to read like a waterfall.

IF ToNumber([Initial 30 Days Commitment])<=30
OR
ToNumber([Initial 30 Days NOI])<=30
OR
ToNumber([Initial 30 Days Denial])<=30
OR
ToNumber([Initial 30 Days Withdrawn])<=30

if it fails all of these and/or the other dates are blank I need it to read a FAIL. 

something like works for 2 day columns but I cant get it to work with more day columns.

IF (ToNumber([Initial 30 Days Commitment])<=30 && IsNull([Initial 30 Days NOI]))
OR (ToNumber([Initial 30 Days NOI])<=30 && IsNull([Initial 30 Days Commitment]))
THEN 'Pass'
ELSE 'Fail'
ENDIF

[alexnajm]

If you need it to act like a waterfall, build it like a waterfall - continue to do IF... ELSEIF... ELSEIF... as many times as needed before your final ELSE ENDIF.

Otherwise look at your operator - maybe you need AND instead of OR! Without a clear example / breakdown of the logic you want, it'll be hard to help further so a sample input and expected output would be beneficial

[Colman_Hendershot · Accepted solution]

@CRogers22 I took a crack at this question, using an alternative approach other than complex if statements. The result is a bit longer of a workflow but let me know if it solves what you are after. 

My question is if any one of the tests passes, then the result should be a pass (even if another test fails)? 

Thanks,

[PangHC]

@CRogers22 if possible, you can convert the column to number datatype first, it will easier for the formula.


if it contains text, then you may need to use regex_match to validate the number.

if it convert to number, you can remove the regex_match, just simple min([field1],[field2])

[CRogers22]

@Colman_Hendershot 

"My question is if any one of the tests passes, then the result should be a pass (even if another test fails)? "

That's correct.

[alexnajm]

@CRogers22 I am having trouble understanding what the remaining issue is - can you clarify your input and your necessary output, and any bit of logic we should know about? Providing a sample input file would be greatly appreciated too

[CRogers22]

@alexnajm no remaining issue now.

@Colman_Hendershot solved it.

Thank you all!