Formula for "Starting Next" and "Ending Next"

Audry89

Hello!

I'm struggling with a formula.

I have a Smartsheet template of a Simple Project Portfolio, and within the dashboard display it has a feature for the project "Starting Next" and the project "Ending Next". However, there must be something wrong because it shows just the projects with the earliest start and end date (in the past).

I'm interest on having displayed the project that starts closer in the future, from today onwards, that don't have the status of "In progress" or "completed". And also I want displayed the project that will end sooner in the future, from today onwards, that don't have the status of Delayed or Completed.

Can someone help me to generate the right formula to display this? I'd like to collect the projects "Starting Next" and "Ending Next" from my project sheet.

I have a column with:

• Start date
• Status
• End date
• Project
• project Code

Thanks!

Audry

Paul Newcome

Try another range/criteria set in your COLLECT function.

=MIN(COLLECT({End Date}, {End Date}, @cell> TODAY(), {Status}, @cell <> $Metric$15))

Paul Newcome

Are you able to provide screenshots for reference?

Audry89

Sure!

Here are some screenshots.

This is the dashboard I want to have displayed: There, I want to see what is the project that will start next (with its code, the description and the start date), also I want to see the project that will end next (with its code, a description and end date). The problem now is that it's providing me dates from the past, and I need the formula to only who future dates from today onwards.

This is the formula currently used to collect the projects Starting Next. This is the one I need to modify so that it only collects projects that will start next in the future. Metric 12 refers to projects with the status "in process".

This is the formula currently used to collect the projects Ending Next. Again, I need it modified so that it only collects projects that will be ending in the future. Metric 15 refers to projects with the status of "delayed".

Let me know if you need more information. I appreciate your help!

Thank you!

Audry

Paul Newcome

Try another range/criteria set in your COLLECT function.

=MIN(COLLECT({End Date}, {End Date}, @cell> TODAY(), {Status}, @cell <> $Metric$15))

Audry89

Thank you very much! It works now! :)

Paul Newcome

Happy to help. 👍️