How to assign time durations to each dropdown value in a column?
Wondering if there is a way to assign a duration based on the value selected which would automatically add on to the potential start date and calculate an estimated completion date after a form user had made their selections.
Thanks in advance. I have no idea where to begin with this so no formula to share.
Answers

You would need to use a nested IF.
=[Potential Start Date]@row + (7 * IF(OR([Deal Structure]@row = "Direct Lease", [Deal Structure]@row = "Company"), 0, IF([Deal Structure]@row = "NonUnion", 3, IF([Deal Structure]@row = "On Site Project", 4, IF([Deal Structure]@row = "Union", 5, ..........................)))))))

Thanks Paul. Just for clarity, I am putting this in the “Estimated Completion” column correct?
And the ‘7’ represents one week?
Here's my failed attempt:
=[Potential Start Date]@row + (7 * IF(OR([Union/Non Union Labor]@row = "Union", 6, [Union/Non Union Labor]@row = "NonUnion", 3, IF([Deal Structure]@row = "Direct Lease", 0, IF([Deal Structure]@row = "On Site Project", 4, IF([Deal Structure]@row = "Modular", 8, IF([Funding Source]@row = "Company", 0, IF([Funding Source]@row = "Client", 10, IF([Funding Source]@row = "Union", 5, IF([Funding Source]@row = "Landlord", 8))))))))))

That is correct.
The issue is your syntax. I intentionally chose the ones I used to help show a pattern. The first one I used is the only one that has two different options that would output the same number. That's where the OR comes in.
=IF(OR(this is true, that is true), "output this", .................
The rest are all individual arguments for each number, so no or is needed in those IF statements.
=IF(OR(this is true, that is true), "output this", IF(something else is true, "output, something else", ...............................
You should be able to start with what I posted (updating the column names(s) as needed) and just continue with the regular nested IF syntax for the options that I didn't type out.

I understand what you are saying about the OR, but I'm not even 100% sure that these are the correct durations for each value so I would like for them to be interchangeable if they need to be updated.
With that being said, I updated the durations assigned to the values so I can lose the OR function and something still isn't right.
Now I have this:
=[Potential Start Date]@row + (7 * IF([Union/Non Union Labor]@row = "NonUnion", 6, IF([Union/Non Union Labor]@row = "Union", 3, IF([Deal Structure]@row = "Direct Lease", 1, IF([Deal Structure]@row = "On Site Clinic", 4, IF([Deal Structure]@row = "Modular", 8, IF([Funding Source]@row = "Company", 2, IF([Funding Source]@row = "Client", 10, IF([Funding Source]@row = "Union", 5, IF([Funding Source]@row = "Landlord", 8))))))))))
Although I don't have an error, it doesn't seem to be working properly. The Union/Non Union Labor column works but when I make different selections in the Deal Structure or Funding Source columns, the date doesn't update.

Is there one column in particular that overrides another column?

Not that I'm aware of...

So if the columns from left to right read Union, On Site Project, and Client, what would be the expected output?

6 + 4 + 10 = 20. So 20 weeks from the start date of 3/13/23 would be 7/31/23.

Ah. Ok. I misunderstood what you were trying to do. In that case you would need three separate nested IF statements (one for each column) and then add them together.
=IF(............., IF(...................)) + IF(..............., IF(................, IF(....................))) + IF(..............., IF(................, IF(...................., IF(............))))

When I change the values in the first two columns the completion date adjusts no problem. I cannot guarantee it will update, however, when any changes are made to the deal structure or funding source. It's very hit and miss. What am I missing?
=[Potential Start Date]@row + (7 * IF([Union/Non Union Labor]@row = "NonUnion", 3, IF([Union/Non Union Labor]@row = "Union", 6) + IF([Deal Structure]@row = "Direct Lease", 1, IF([Deal Structure]@row = "On Site Clinic", 4, IF([Deal Structure]@row = "Modular", 8) + IF([Funding Source]@row = "Company", 2, IF([Funding Source]@row = "Client", 10, IF([Funding Source]@row = "Union", 5, IF([Funding Source]@row = "Landlord", 8))))))))

I also tried this formula:
=[Potential Start Date]@row + (7 * IF([Union/Non Union Labor]@row = "NonUnion", 3, IF([Union/Non Union Labor]@row = "Union", 6)) + IF([Deal Structure]@row = "Direct Lease", 1, IF([Deal Structure]@row = "On Site Clinic", 4, IF([Deal Structure]@row = "Modular", 8))) + IF([Funding Source]@row = "Company", 2, IF([Funding Source]@row = "Client", 10, IF([Funding Source]@row = "Union", 5, IF([Funding Source]@row = "Landlord", 8)))))
...and still not able to get the Estimated Completion date to update accordingly when different values are put in the Deal Structure or Funding Source columns.

Lets try using a set of parenthesis to add up the IFs first before multiplying by 7.
=Date@row + (7 * (IF(.....) + IF(.....) + IF(.....)))
=[Potential Start Date]@row + (7 * (IF([Union/Non Union Labor]@row = "NonUnion", 3, IF([Union/Non Union Labor]@row = "Union", 6)) + IF([Deal Structure]@row = "Direct Lease", 1, IF([Deal Structure]@row = "On Site Clinic", 4, IF([Deal Structure]@row = "Modular", 8))) + IF([Funding Source]@row = "Company", 2, IF([Funding Source]@row = "Client", 10, IF([Funding Source]@row = "Union", 5, IF([Funding Source]@row = "Landlord", 8))))))

That worked!!! It's working perfectly, thankyou so much!
Now I have another problem... it's not working in my form:

It won't populate within the form itself. The calculations are run in the sheet which means the data must be in the sheet (form submitted) for the calculation to run.

That's a shame. Is there a different way to create something else within smartsheet that will look like a form but function like sheet? Or another software you could recommend if smartsheet doesn't have this capability?
Help Article Resources
Categories
Check out the Formula Handbook template!