IF AND function

Ed Franklin

First time here and new to Smartsheet.

In a cell I have a number. Say 400

In another cell I have a specific date.  Say January 1, 2020

What I want to do as a task start date in another cell is:

If the number in the cell falls within a number range on another sheet I want the task start date to be, specific date – 18 months and so on.  So if the number is 2500 then it will be specific date – 36 months.

This is the information contained in the other sheet and each number set is in a different cell.

1 - 499  18 months

500 - 1999 24 months

2000 - 4999 36 months

5000 - 9999 48 months

10000 - 14999 60 months

15000 - 35000 72 months

Ed Franklin

Here is what I have tried so far.  Any help would be appreciated.  I realize that the number of months needs to be converted somehow and I don’t know how.

=IF ([Area]1 > {Table Range 1}, [DATE]2 – {Table Range 2} , ([Area]1 > {Table Range 3}, [DATE]2 – {Table Range 4} , ([Area]1 > {Table Range 5}, [DATE]2 – {Table Range 6} , ([Area]1 > {Table Range 7}, [DATE]2 – {Table Range 8} , ([Area]1 > {Table Range 9}, [DATE]2 – {Table Range 10} , ([Area]1 > {Table Range 11} , [DATE]2 – {Table Range 12}))))))

[Area]1 is a specific number contained in a date formatted column, say 400 is the number

[DATE]2 is a specific date contained in a date formatted column, say 01/01/20

Odd and even numbered {Table Range} is a specific number in a text or number formatted column in a separate sheet

Odd numbered {Table Range} represents a number 15,000, 10000, 5000, 2000, 500, or 1

Even numbered {Table Range} represents a number in months 72, 60, 48, 36, 24, or 12

So: =IF ([Area]1 >{Table Range 1}, [DATE]2 – {Table Range 2}……..if not then the formula continues until TRUE

=IF (400 >15000, 01/01/20 – 72 months, (400 >10000, 01/01/20 – 60 months, (400>5000, 01/01/20 –48 months, 400>2000, 01/01/20 – 36 months, (400 >500, 01/01/20 – 24 months, (400 > 1}, 01/01/20 – 18 months))))))

The last statement would ring TRUE on the last calculation so the answer would populate as a date. 07/01/18