How to assign time durations to each dropdown value in a column?

Graham Cracker

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.

Paul Newcome

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, ..........................)))))))

Graham Cracker

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))))))))))

Paul Newcome

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.

Graham Cracker

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.

Paul Newcome

Is there one column in particular that overrides another column?

Graham Cracker

Not that I'm aware of...

Paul Newcome

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

Graham Cracker

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

Paul Newcome

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(............))))

Graham Cracker

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))))))))

Graham Cracker

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.

Paul Newcome

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))))))

Graham Cracker

That worked!!! It's working perfectly, thank-you so much!

Now I have another problem... it's not working in my form:

Paul Newcome

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.

Graham Cracker

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?