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

Find more posts tagged with

Comments

Andrew Duval

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!

Heath Sanders

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

Andrew Duval

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

Heath Sanders

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

Andrew Duval

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

Heath Sanders

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

Heath Sanders

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

Shaine Greenwood

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