Blank Dates reading as Pass

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.

Capture 5.PNG

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.

Capture 6.PNG

Reporting

Accepted answers

Colman_Hendershot

@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,

Solved Attempt.yxmd

All comments

alexnajm

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

alexnajm

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

Solved Attempt.yxmd

AncientPandaman

@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])

Screenshot 2025-01-17 100349.png

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703