# 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.

• 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 = "Non-Union", 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 = "Non-Union", 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 = "Non-Union", 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 = "Non-Union", 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 = "Non-Union", 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 = "Non-Union", 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, thank-you 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!