How to add an IF statement to a formula that returns the number of months between two dates

Options

Hello Community,

I'm trying to write a formula that returns the number of months remaining in a term if a date is provided, and if no date return "NA".

I have =ROUND((NETDAYS(TODAY(), [Lease Expiration]@row) / 365) * 12), but not sure how to make this formula return "NA" on blanks instead of an error:


Best Answers

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    edited 07/12/22 Answer ✓
    Options

    This should do the trick.

    =IF( ISDATE([Lease Expiration]@row), ROUND((NETDAYS(TODAY(), [Lease Expiration]@row) / 365) * 12), "N/A")

    I guess you could also use this as well

    =IFERROR(ROUND((NETDAYS(TODAY(), [Lease Expiration]@row) / 365) * 12), "N/A")

    but I don't like using an Error function as part of planned code

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Answer ✓
    Options

    @Carmenlita Ashbourne .. Sort of ;-)

    If you are looking at using IF statements you might want to look here

    They take the function..

    IF( logical_expression, value_if_true [ value_if_false ])

    So what I wrote in the formula was "IF the Lease expiration contains a date then use Camenlita's formula otherwise put "N/A"

    So for status formulas, it really comes down to your LOGICAL_EXPRESSION which is like the question

    So for a status, you may want to look at the functions ISBLANK or ISTEXT

    So something like

    =IF (ISBLANK(Status@row),"Missing Status","Status is Good")

    This can be flipped with NOT

    =IF(NOT(ISBLANK(Status@row)),"Status is Good","Missing Status")

    Also, Smartsheet has a really good Smartsheet Formula Template which goes through all the different types of formulas.. you can play with it and break it.. so don't worry.. have a look for it in the templates area.

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

Answers

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    edited 07/12/22 Answer ✓
    Options

    This should do the trick.

    =IF( ISDATE([Lease Expiration]@row), ROUND((NETDAYS(TODAY(), [Lease Expiration]@row) / 365) * 12), "N/A")

    I guess you could also use this as well

    =IFERROR(ROUND((NETDAYS(TODAY(), [Lease Expiration]@row) / 365) * 12), "N/A")

    but I don't like using an Error function as part of planned code

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • Carmenlita Ashbourne
    edited 07/12/22
    Options

    Awesome it worked, thank you so much! Will this work on status formulas as well?

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Answer ✓
    Options

    @Carmenlita Ashbourne .. Sort of ;-)

    If you are looking at using IF statements you might want to look here

    They take the function..

    IF( logical_expression, value_if_true [ value_if_false ])

    So what I wrote in the formula was "IF the Lease expiration contains a date then use Camenlita's formula otherwise put "N/A"

    So for status formulas, it really comes down to your LOGICAL_EXPRESSION which is like the question

    So for a status, you may want to look at the functions ISBLANK or ISTEXT

    So something like

    =IF (ISBLANK(Status@row),"Missing Status","Status is Good")

    This can be flipped with NOT

    =IF(NOT(ISBLANK(Status@row)),"Status is Good","Missing Status")

    Also, Smartsheet has a really good Smartsheet Formula Template which goes through all the different types of formulas.. you can play with it and break it.. so don't worry.. have a look for it in the templates area.

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!