If Then Date

GSA Rob

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 0-50k, I want to output a date 90days after "3. RWA Acceptance"; 50-250k 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!!

Nick Korna

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!

Nick Korna

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!

GSA Rob

This worked perfectly! Thanks so much!!