Skip to main content
Solved

IF, Then formula

  • July 29, 2022
  • 3 replies
  • 41 views
E
Forum|alt.badge.img+4

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?

Best answer by EV_TRAINING

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

3 replies

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • July 29, 2022

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"
        )
    )
)
Hannah_Wiginton
E
Forum|alt.badge.img+4
  • EV_TRAINING
  • Author
  • New Participant
  • Answer
  • July 29, 2022

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

Ben_Young1
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • July 30, 2022

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"
    )
)
Terms & ConditionsAccessibility statement