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()
=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
-
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).
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives