Need help with combing If or and if and statements

[james_pease]

Hello all, I am trying to pass multiple tests that return "yes." The first test is to see if an employees last pay day is greater than 21 days, the second is to test wether their last pay date is blank and if so, return yes if their hire date is greater than 21 days from today. 

Here is what I have so far:

Active No Hours = IF(DATEDIFF('ADP Active Employees'[Last Pay Date], TODAY(), DAY)>21, "Yes")

||IF(AND(LEN('ADP Active Employees'[Last Pay Date]) <0,
DATEDIFF('ADP Active Employees'[Last Pay Date], TODAY(), DAY)>21),"Yes")

I have tried using ISBLANK with "If and" but it kept saying two few arguments were passed, which is what led me to try "If and" with a Len <0. Thank you in advance!

[tamerj1]

Hi @james_pease 

please try

Active No Hours =
IF (
    DATEDIFF ( 'ADP Active Employees'[Last Pay Date], TODAY (), DAY ) > 21
        || AND (
            LEN ( 'ADP Active Employees'[Last Pay Date] ) < 0,
            DATEDIFF ( 'ADP Active Employees'[Last Pay Date], TODAY (), DAY ) > 21
        ),
    "Yes"
)

[james_pease]

Thank you, but for some reason its not reutrning the employees with an blank last pay date (empty cell). Its passing the first arguement fine, but for some reason, its not testing if the last pay date is blank, then return yes if hire date is > than 21 days from today.

Here is a screenshot, I blacked out names for privacy. Notice how it is not returing a "yes" for those without a last pay date and their hire date is > today + 21 days?

AHHH so sorry, I needed to change the second clause to

 DATEDIFF ( 'ADP Active Employees'[Hire/Rehire Date], TODAY (), DAY ) > 21

Works like a charm!

[tamerj1 · Accepted solution]

@james_pease 

Please try

Active No Hours =
IF (
    DATEDIFF ( 'ADP Active Employees'[Last Pay Date], TODAY (), DAY ) > 21
        || AND (
            ISBLANK ( 'ADP Active Employees'[Last Pay Date] ),
            DATEDIFF ( 'ADP Active Employees'[Last Pay Date], TODAY (), DAY ) > 21
        ),
    "Yes"
)

[james_pease]

Sadly, same result:

Notice how there are 1501 blanks, I am expecting several to say "yes" in the 'Active No Hours column'

[james_pease]

I have also tried the following:

IF(OR(DATEDIFF('ADP Active Employees'[Last Pay Date], TODAY(), DAY)>21,

IF(AND(LEN('ADP Active Employees'[Last Pay Date]) <0,

DATEDIFF('ADP Active Employees'[Last Pay Date], TODAY(), DAY)>21),"Yes")), "Yes")

The second arguement isnt being passed because its using it as the true statement versus a second argument.

But the results are less than what I am expecting, the