Assign duration on dropdown list items and update dates accordingly

EdwardH

Hello All,

I am trying to see if it is feasible to add a specific duration (in days) to a respective item in a dropdown list.

For example, I have the following dropdown list:

1 - Review feasibility

2 - Design Review

3 - MFG start

4 - Inspection

And on each item, I would like to capture the duration as follows:

1 - Review feasibility = 10 days

2 - Design Review = 5 days

3 - MFG start = 20 days

4 - Inspection = 10 days

Then I have a column for the project's "start date", "next milestone date", and "end date".

So based on the selection picked in the dropdown list, the "next milestone" and "finish" dates will get updated automatically.

Thank you in advance for any insights on getting this set up!

Paul McGuinness

Hi @EdwardH

Really interesting question.

I was able to make this work but it did require 4 helper columns to do so that you can hide. Below is the working visual

Formulas:

Next milestone date column - =MIN(COLLECT([1 - Review feasibility]@row:[4 - Inspection]@row, [1 - Review feasibility]@row:[4 - Inspection]@row, >TODAY()))

End date column - =[Start date]@row + SUM(IF(CONTAINS("1 - Review feasibility", Selection@row), 10, ""), IF(CONTAINS("2 - Design Review", Selection@row), 5, ""), IF(CONTAINS("3 - MFG start", Selection@row), 20, ""), IF(CONTAINS("4 - Inspection", Selection@row), 10, ""))

Helper columns are used to calculate the next floating date for the next milestone date

1 - Review feasibility - =IF(CONTAINS("1 - Review feasibility", Selection@row), [Start date]@row + 10, "")

2 - Design Review - =IF(CONTAINS("2 - Design Review", Selection@row), MAX([Start date]@row + 5, [1 - Review feasibility]@row + 5), "")

3 - MFG start - =IF(CONTAINS("3 - MFG start", Selection@row), MAX([Start date]@row + 20, [1 - Review feasibility]@row + 20, [2 - Design Review]@row + 20), "")

4 - Inspection - =IF(CONTAINS("4 - Inspection", Selection@row), MAX([Start date]@row + 10, [1 - Review feasibility]@row + 10, [2 - Design Review]@row + 10, [3 - MFG start]@row + 10), "")

Formulas can be converted to column formulas as required,

Hope that helps

Thanks

Paul

EdwardH

Thank you so much, Paul!

I tried to set it up, but I keep getting an #UNPARSEABLE error. I attached some screenshots to see if I am missing something here:

Thank you for your guidance and support on this!

Best,

Ed

Genevieve P.

Hi @EdwardH

Just checking in to see if you were able to get this to work?

If not, can you show us all of the formulas you've input? If one of them says Unparseable then that will reflect in all the other formulas that are referencing it.

EdwardH

Hello Genevieve,

Thank you for following up on this topic. I couldn't get it to work...

Below are all the formulas I put in place and a screenshot of the columns layout + drop down:

2023-05-11 09_12_58-Window.jpg

"Next milestone" column:

=MIN(COLLECT([1 - Review feasibility]@row:[4 - Inspection]@row, [1 - Review feasibility]@row:[4 - Inspection]@row,

"End date" column:

=[Start date]@row + SUM(IF(CONTAINS("1 - Feasibility", Selection@row), 10, ""), IF(CONTAINS("2 - PPCO", Selection@row), 5, ""), IF(CONTAINS("3 - RFQ", Selection@row), 20, ""), IF(CONTAINS("4 - CB2 Process", Selection@row), 10, ""))

"1- Review feasibility" column:

=IF(CONTAINS("1 - Feasibility", Selection@row), [Start date]@row + 10, ""

"2 - Design Review" column:

=IF(CONTAINS("2 - PPCO", Selection@row), MAX([Start date]@row + 5, [1 - Feasibility]@row + 5), "")

"3 - MFG Start" column:

=IF(CONTAINS("3 - RFQ", Selection@row), MAX([Start date]@row + 20, [1 - Feasibility]@row + 20, [2 - PPCO]@row + 20), "")

"4 - Inspection" column:

=IF(CONTAINS("4 - CB2 Process", Selection@row), MAX([Start date]@row + 10, [1 - Feasibility]@row + 10, [2 - PPCO]@row + 10, [3 - RFQ start]@row + 10), "")

Let me know if all makes senses here

Thank you in advance for your support

++

Genevieve P.

Hi @EdwardH

Thank you for this information! I believe I know what the issue is here. You have Selection@row multiple times in these formulas, but your column name is Steps (15) so [Steps (15)]@row is what you would want to reference instead!

For example:

=IF(CONTAINS("1 - Feasibility", Selection@row), [Start date]@row + 10, ""

Should be:

=IF(CONTAINS("1 - Feasibility", [Steps (15)]@row), [Start date]@row + 10, ""

Does that make sense?

Cheers,

Genevieve

EdwardH

Thank you so much!

It fixed the formula issues, but now its not really doing what I expected...

I want to put the start date then based on the dropdown selection it calculate/generates the date on the columns "Next Milestone" & "End Date"

Does it make sense?

Thank you

++

Genevieve P.

Hi @EdwardH

These formulas assume that all of your columns are Date type of columns, is that correct?

For example:

"1- Review feasibility" column should be a date column. Then this formula would add 10 days to the Start Date if it's selected:

=IF(CONTAINS("1 - Feasibility", [Steps (15)]@row), [Start date]@row + 10, "")

EdwardH

Hello Genevieve,

Yes, they are all dates!

But my goal is to have, based on the selection picked in the dropdown list (Steps (15)), the "Next milestone" and "End date" dates get updated automatically, not the start date, as this will get put in manually.

Thank you for your responses

Genevieve P.

Hi @EdwardH

That's great! If they're all dates, it means that your columns will auto-populate with days that are X number of days after the Start Date.

Then the formula in the "Next Milestone" cell looks across those dates and brings back the MIN date to find out the next one. Is the problem that you're seeing here that it doesn't change when that minimum date is in the past?

In regards to the "End Date" formula, it adds the total number of days associated with all the different selections to figure out the End Date. If that's not what you want, can you explain when you define the End Date? Is it the oldest date in the row?

=MAX([1 - Review feasibility]@row:[4 - Inspection]@row)

Cheers,

Genevieve

EdwardH

Hi Genevieve,

Let me try to reiterate what I am trying to achieve here. So first I am trying to see if it is feasible to add a specific duration (in days) to a respective item in a dropdown list.

So I have the following dropdown list:

1 - Review feasibility

2 - Design Review

3 - MFG start

4 - Inspection

And on each item, I would like to capture the duration as follows:

1 - Review feasibility = 10 days

2 - Design Review = 5 days

3 - MFG start = 20 days

4 - Inspection = 10 days

Then I have a column for the project's "Start date", "Next milestone date", and "End date".

So based on the selection picked in the dropdown list, the "Next milestone date" and "End date" dates will get updated automatically based on the start date that I input manually.

The goal here is to track potential upcoming completion dates based on the start date and dropdown list tasks.

I would also need to capture delays at some point...if someone misses the "Next milestone date"...that is next step :D

Let me know if it makes senses and/or if you need any clarifications.

Thank you in advance!

++

Genevieve P.

Hi @EdwardH

Yes, exactly! 🙂

Based on this description, it looks like your first 4 columns are working as you expect: looking at a dropdown selection, they add a specific number of days (determined by your Start Date) to return a date for that item in the list.

@Paul McGuinness was very clever and based all the other columns off of both the Start Date and if there had been other selections, so you're not only looking at the Start Date but at what else needs to be done:

Now that you have dates for each Milestone, the "Next Milestone" brings back the MIN date out of those selections, showing what's coming next after the start date.

However since you're noting that this isn't working, I wonder if what you want is actually to bring back whatever the next date is compared to Today, is that correct? Meaning that if the Start Date hasn't started yet, the Milestone Date would be the Start Date. If it HAS started, then the Next Milestone would be the date that's immediately after Today.

If this is what you meant, try this:

=IF([Start Date]@row > TODAY(), [Start Date]@row, MIN(COLLECT([1 - Review feasibility]@row:[4 - Inspection]@row, [1 - Review feasibility]@row:[4 - Inspection]@row, >=TODAY())))

This will only bring back the next date in the list if it's greater than Today, meaning it's the next one in the future.

For the End Date, if you just mean the last date in the list, you can use the MAX formula above:

=MAX([1 - Review feasibility]@row:[4 - Inspection]@row)

I will note that it looks like some of your column names may be different. We're using [Start Date] in our formulas but it looks like you may have a . at the end, so you'll need to reference [Start date.] instead. I would also advise checking that your values in "Quotes" match exactly the values in your dropdowns, or you'll get the wrong Count.

If this still doesn't make sense or hasn't helped, it would be useful to know what your definition of what a "Next Milestone Date" is, along with screen captures showing the formulas you've used so far and if you're getting an error or incorrect result.

Thanks!

Genevieve

EdwardH

Hello Genevieve,

Thank you for your response!

But it still doesn't work...I could quickly show you on a 5-10mins zoom call?

Then once we get it to work I can post it on here

Thanks in advance!

++

Genevieve P.

Hi @EdwardH

It looks like you're licensed on a plan that include Pro Desk sessions: these are 30-minute coaching sessions over screen share. They don't build solutions but they could take a look at your sheet directly and make recommendations. Here's the link!

Cheers,

Genevieve