Skip to main content

I’m trying to write a formula that says. If {Inspection Date} is not blank and the number is greater than 730, then “No” or If {Inspection Date} is not blank and less than 730 then “Yes”.


I keep getting hung up with the blank fields.


This is my current formula: IF({Inspection Date Formula}!= BLANK(),AND({Inspection Date Formula}>730,“No”,“Yes”)). The output is now a 1 for NO and a 0 for Yes. Blank fields are still blank which is what I want.

How do I get the 1 and 0 to say No and Yes?

Hey @EV_TRAINING!

Welcome to the forums!


Here’s a way to format a formula like that:


IF(

    {Inspection Date},

    IF(

        {Number} > 730,

        "No",

        IF(

            {Number} < 730,

            "Yes"

        )

    )

)


For this formula, if the {Inspection Date} field is blank, then the formula will return blank.

I haven’t tested it in a sandbox, but I think that if the number field is blank, then the formula will just return “Yes”. This happens because 730 is technically more than the presence of no data.


If you want the formula just to return blank if there is no number, then you can use this formula:


IF(

    AND(

        {Inspection Date},

        {Number}

    ),

    IF(

        {Number} > 730,

        "No",

        IF(

            {Number} < 730,

            "Yes"

        )

    )

)

YES!! This is exactly what I needed. I was overthinking it. Thank you1!

Hey @EV_TRAINING!

Welcome to the forums!


Here’s a way to format a formula like that:


IF(

    {Inspection Date},

    IF(

        {Number} > 730,

        "No",

        IF(

            {Number} < 730,

            "Yes"

        )

    )

)


For this formula, if the {Inspection Date} field is blank, then the formula will return blank.

I haven’t tested it in a sandbox, but I think that if the number field is blank, then the formula will just return “Yes”. This happens because 730 is technically more than the presence of no data.


If you want the formula just to return blank if there is no number, then you can use this formula:


IF(

    AND(

        {Inspection Date},

        {Number}

    ),

    IF(

        {Number} > 730,

        "No",

        IF(

            {Number} < 730,

            "Yes"

        )

    )

)

You probably want a value if the number is exactly 730. Depending on what the value should be you can use either > or >=


IF(

    AND(

        {Inspection Date},

        {Number}

    ),

    IF(

        {Number} > 730,

        "No",

         "Yes"

    )

)

Reply


Terms & Conditions