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.

Help Please =IF(TODAY()

Heath Sanders
edited 12/09/19 in Archived 2017 Posts

 =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 

 

Thanks in advance

Heath

 

 

Comments

  • 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 

     

     

     

    Capture.PNG

  • 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"))

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