IF AND function

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

Find more posts tagged with

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Automation not working
I have a Smartsheet automation setup to reminder the planning to complete their sections. It is setup to remind weekly until completed box is checked. They received 2 reminders and then the reminders stopped. Any idea what could be wrong? I can't tell that anything has changed in the sheet. Thanks Freda
How to disable popups that obscure my formula?
I'm editing formulas in a Sheet Summary. However, I'm prevented from seeing the formula by an annoying "helpful" popup that obscures my view. I can't figure out how to get rid of the popup. Does anyone know?
Workflow Error: "Recipients: Edit workflow to fix recipients list"
I am trying to create a workflow and for some reason it gives me an error with the email I entered as the recipient for my Alert. It is an email that I know works, it is shared to the sheet, and the access to the sheet is unrestricted. I am perplexed at what the issue is. Anyone else get this and know what the issue is?