#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

edited 12/09/19

=IF(TODAY() - [Expected Close Date]2 - 90>120, "3-6 Mths")

If todays date 08/09/2017 Nz or 09/08/2017 Us (Built in calendar in Smart Sheet)  looks at the date I entered in to my Expected Close Date column  14/02/2017 Nz or 02/14/2017 Us then it will return "3-6 Mths"   being that 14 Feb date is between 90-120 days ????

Am I on the right track or is someone going to show this kiwi boy up again

Heath

• Hello Heath,

You can use the following formula in the column that you want "3-6 Mnths" to display (this formula assumes that the close date is in the future):

=IF(AND(([Expected Close Date]2-TODAY()) > 90, ([Expected Close Date]2 - TODAY()) < 120), "3-6 Mths")

Using an IF(AND()) formula will allow you to set multiple criteria (in your case: greater than 90 days, but also less than 120 days). Also, because you're calculating a value based on a future date, you will want to subtract TODAY() from the [Expected Close Date]

Hope this helps!

• Hi Andrew,

Thanks for sorting that mate. Very helpful Thankyou.

Just taking it to the next level 3-6 Mths  7-12 Mths etc. I took what you did and try to add on it...back fired on me...

=IF(AND(([Last Contact Date]3 - TODAY()) > 90, ([Last Contact Date]3 - TODAY()) < 180), "3-6 Mths", IF(AND(([Last Contact Date]3 - TODAY())) > 210, ([Last Contact Date]3 - TODAY() < 365), "7-12 Mths"))

• Hello Heath,

It looks like you have an extra ) after TODAY() right before >210. You can either delete the extra ) or you can copy and paste the formula below and it should work.

=IF(AND(([Last Contact Date]3 - TODAY()) > 90, ([Last Contact Date]3 - TODAY()) < 180), "3-6 Mths", IF(AND(([Last Contact Date]3 - TODAY()) > 181, [Last Contact Date]3 - TODAY() < 365), "7-12 Mths"))

• Hello Andrew,

Thanks again for stepping in and help out. I've attached a screen shoot to view what is going on....

It works perfectly for future dates (Expected close date) but not for past dates (Last contact date).

Once I get the formatting right I can put some conditional formatting around that.. Thanks again for your hugh help mate

• Hi Heath,

For dates in the past, you'll want to flip-flop [Last Contact Date] and the TODAY() function so that the last contact date is subtracting from today's date:

=IF(AND((TODAY() - [Last Contact Date]3) > 90, (TODAY() - [Last Contact Date]3) < 180), "3-6 Mths", IF(AND((TODAY() - [Last Contact Date]3) > 181, (TODAY() - [Last Contact Date]3) < 365), "7-12 Mths"))

• Hi Andrew,

Thanks again for helping out with all these date formulas, I had time to take them to another level with coloring cells and font using conditional formatting. A bit slow on my reply due to recent surgery, do appreciate the time you have put aside..

Cheers Mate

• Hi Andrew, You help me with this formula, just enter into the sheet and its come back with #UNPREASABLE ? The only thing that's changed is the column name which is what I added so I could have future date column as well as past date colomn

=IF(AND(([Expected Close Date]1-TODAY()) > 90, [Expected Close Date]1-TODAY()) < 180),"3-6",If(AND([Expected Close Date]1-TODAY()) >181,[Expected Close Date]1-TODAY()) <365),"7-12Mths"))

• Employee

Hi Heath,

#UNPARSEABLE errors occur if you reference a column/cell that doesn't exist or if you have too many parenthesis in your function.

Looks like your first IF(AND() section has an extra close paren between TODAY() and <180. (Or maybe you left out an open paren.)

I'd also double-check to make sure your formula is referencing the correct column (check for typos).

This discussion has been closed.