Nested IF, AND Function

Natasha D

Good morning

I am trying to write a formula that incorporates the following criteria (screenshot below):

IF "Date Received" is <= "Closing Date" AND "Received" is checked, then "Application Received"

IF "Date Received" is > "Closing Date" AND "Received" is checked, then " Late Application"

IF "Received" is not checked, then "No Application Received"

I have tried so many variations and being a novice to smartsheet, I can't seem to get it right. I will have the "Closing Date" hidden once completed.

Is anyone able to assist?

Thanks in advance!

Mike Wilday

Hi Natasha,

Try this formula.

=IF(AND([Date Received]@row <= [Closing Date]@row, Received@row = 1), "Application Received", IF(AND([Date Received]@row > [Closing Date]@row, Received@row = 1), "Late Application", IF(Received@row = 0, "Late Application")))

Here are some tips for formulas in Smartsheet:

1. When referencing columns with spaces in their title or that end in a number, always surround the header title with brackets. If the name is a single word, then you don't need the brackets.
2. @row can be used to reference the column and field that is in the same row as your formula. It can replace the row number which is usually required for a formula. This makes your formula less-likely to break when you copy it from one row to another. It will always reference the row it is in.
3. AND formulas are tricky because they go outside of the normal flow of thought. Instead of this AND that, you have to put the AND at the beginning of the formula and then state the criteria... AND(this, that).

Hope that helps!

Paul Newcome

Another option:

=IF(Received@row = 0, "No Application Received", IF([Date Received]@row <= [Closing Date]@row, "Application Received", "Late Application"))

Paul Newcome

@Natasha D The extra tips came from @Mike Wilday. But you're welcome anyway. Haha.

But here are two different things that I'll do when creating formulas to help with the column names...

Typically I will change the column name to something easy to type that doesn't have any special characters, numbers, or spaces in it. It doesn't even have to make sense. Just something that is strictly alphabetical characters and is easy to type. One of my typical fall-backs for this is "qwe". Top left three letters on my keyboard. Next column would be "zxc". Bottom left three. On and on. If I am going to be writing a formula in a part of the sheet where the columns I am referencing are NOT visible, I just remove spaces and numbers and whatnot. So "Start Date" become StartDate" and "Finish Date" becomes "FinishDate", so on and so forth.

Doing this removes the need for square brackets which I am really good at forgetting.

After you type out your formula(s), you can then go back and change the column names to whatever, and the formulas will automatically update adding square brackets as needed. This REALLY helps when you have a longer column name or one that is a little more complex with multiple special characters and whatnot.

Another thing I do is for ranges (but can be used on single cell references as well), and that is to simply click and drag a few cells down the column I want to reference, or I won't pay attention to the row and I'll just drag across a row to cover all of the columns I want.

This goes ahead and gets your column names in there with the correct square brackets and spellings and everything else, and all you have to do from there is update your row numbers. You can either remove them to reference a column or change them to reference whatever you need.

This can also be done if you are referencing a single cell. Click on any cell within that column and then just update the row number as needed.

Both of those help with making sure column names are entered correctly.

Mike Wilday

Hi Natasha,

Try this formula.

=IF(AND([Date Received]@row <= [Closing Date]@row, Received@row = 1), "Application Received", IF(AND([Date Received]@row > [Closing Date]@row, Received@row = 1), "Late Application", IF(Received@row = 0, "Late Application")))

Here are some tips for formulas in Smartsheet:

1. When referencing columns with spaces in their title or that end in a number, always surround the header title with brackets. If the name is a single word, then you don't need the brackets.
2. @row can be used to reference the column and field that is in the same row as your formula. It can replace the row number which is usually required for a formula. This makes your formula less-likely to break when you copy it from one row to another. It will always reference the row it is in.
3. AND formulas are tricky because they go outside of the normal flow of thought. Instead of this AND that, you have to put the AND at the beginning of the formula and then state the criteria... AND(this, that).

Hope that helps!

Paul Newcome

Another option:

=IF(Received@row = 0, "No Application Received", IF([Date Received]@row <= [Closing Date]@row, "Application Received", "Late Application"))

Natasha D

Thanks Paul and Mike! Both of those work and make complete sense.

I appreciate the help.

Paul, thank you for the extra tips on formulas. I realised number 1 yesterday after watching brackets disappear in other formulas as I was typing them. Will certainly think of this when creating column headings in the future.

Paul Newcome

@Natasha D The extra tips came from @Mike Wilday. But you're welcome anyway. Haha.

But here are two different things that I'll do when creating formulas to help with the column names...

Typically I will change the column name to something easy to type that doesn't have any special characters, numbers, or spaces in it. It doesn't even have to make sense. Just something that is strictly alphabetical characters and is easy to type. One of my typical fall-backs for this is "qwe". Top left three letters on my keyboard. Next column would be "zxc". Bottom left three. On and on. If I am going to be writing a formula in a part of the sheet where the columns I am referencing are NOT visible, I just remove spaces and numbers and whatnot. So "Start Date" become StartDate" and "Finish Date" becomes "FinishDate", so on and so forth.

Doing this removes the need for square brackets which I am really good at forgetting.

After you type out your formula(s), you can then go back and change the column names to whatever, and the formulas will automatically update adding square brackets as needed. This REALLY helps when you have a longer column name or one that is a little more complex with multiple special characters and whatnot.

Another thing I do is for ranges (but can be used on single cell references as well), and that is to simply click and drag a few cells down the column I want to reference, or I won't pay attention to the row and I'll just drag across a row to cover all of the columns I want.

This goes ahead and gets your column names in there with the correct square brackets and spellings and everything else, and all you have to do from there is update your row numbers. You can either remove them to reference a column or change them to reference whatever you need.

This can also be done if you are referencing a single cell. Click on any cell within that column and then just update the row number as needed.

Both of those help with making sure column names are entered correctly.

Mike Wilday

@Paul Newcome

Which I am really good at forgetting..

Aren't we all???? 😂😂😂

Paul Newcome

@Mike Wilday Haha. Too true... Ugh!