# 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:

• ✭✭✭✭✭
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

• ✭✭✭✭✭
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

• ✭✭✭✭✭
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

• edited 07/12/22
Options

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

• ✭✭✭✭✭
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!