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

Carmenlita Ashbourne · 07/12/22

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:

Brent Wilson · 07/12/22

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 Wilson · 07/12/22

@Carmenlita Ashbourne .. Sort of ;-)

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

https://help.smartsheet.com/function/if

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 Wilson · 07/12/22