If Then Date
Good afternoon Smartsheet community. I am trying to get an if/then function to output a date in my sheet. I have a few columns I am checking against and feel like I am close but need some help getting it to work properly. Here is my formula:
=if(AND([Project Type]@row = "N Type"([3. RWA Acceptance Date (PM/Kayla)]@row = DATE(), [3. RWA Acceptance Date (PM/Kayla)]@row+90))
I am searching in the Project Type column for "N Type" projects only, then checking the "3. RWA Acceptance..." to make sure there is a date listed. If there is, then I want to check "Cost ($)" column. If 050k, I want to output a date 90days after "3. RWA Acceptance"; 50250k gets 120 days after "3. RWA Acceptance"; and 180 days for 250k+. This date should be output in column "3a". Does this make sense? I know my formula is lacking a bunch of this info but this is as far as my knowledge has gotten me. HELP!!
Best Answer

Hi @GSA Rob,
You're looking for a nice nested IF/AND formula here, along these lines:
=IF(AND([Project Type]@row = "N Type", [3. RWA Acceptance Date (PM/Kayla)]@row <> "", [Cost ($)]@row >= 0, [Cost ($)]@row <= 50000), [3. RWA Acceptance Date (PM/Kayla)]@row + 90, IF(AND([Project Type]@row = "N Type", [3. RWA Acceptance Date (PM/Kayla)]@row <> "", [Cost ($)]@row > 50000, [Cost ($)]@row <= 250000), [3. RWA Acceptance Date (PM/Kayla)]@row + 120, IF(AND([Project Type]@row = "N Type", [3. RWA Acceptance Date (PM/Kayla)]@row <> "", [Cost ($)]@row > 250000), [3. RWA Acceptance Date (PM/Kayla)]@row + 180, "")))
Sample output using above:
The formula will give a blank result if the conditions aren't met (project type is anything other than N type, no date). It's fairly easy to adjust the brackets if necessary  as your Cost thresholds had the number in both from/to ranges it's currently up to.
Hope this helps, but if I've misunderstood something or you have any problems/questions then just post!
Answers

Hi @GSA Rob,
You're looking for a nice nested IF/AND formula here, along these lines:
=IF(AND([Project Type]@row = "N Type", [3. RWA Acceptance Date (PM/Kayla)]@row <> "", [Cost ($)]@row >= 0, [Cost ($)]@row <= 50000), [3. RWA Acceptance Date (PM/Kayla)]@row + 90, IF(AND([Project Type]@row = "N Type", [3. RWA Acceptance Date (PM/Kayla)]@row <> "", [Cost ($)]@row > 50000, [Cost ($)]@row <= 250000), [3. RWA Acceptance Date (PM/Kayla)]@row + 120, IF(AND([Project Type]@row = "N Type", [3. RWA Acceptance Date (PM/Kayla)]@row <> "", [Cost ($)]@row > 250000), [3. RWA Acceptance Date (PM/Kayla)]@row + 180, "")))
Sample output using above:
The formula will give a blank result if the conditions aren't met (project type is anything other than N type, no date). It's fairly easy to adjust the brackets if necessary  as your Cost thresholds had the number in both from/to ranges it's currently up to.
Hope this helps, but if I've misunderstood something or you have any problems/questions then just post!

This worked perfectly! Thanks so much!!
Help Article Resources
Categories
Check out the Formula Handbook template!