# Next deliverable date based on TODAY

Options
edited 12/09/19

Hi

Many thanks in advance to start!

For each row i have a listed task with a column called Period 1 with a date and a checkbox next to it than when checked marks the date green. Each date represents when that task is next due. Extrapolate that out across the row and i have many dates for every interval the day is due, ie once a week or 7 days, so Period 2 column has a date due and checkbox next to it and so on with is 7 days after the first date and so on.

I have a new column called Next Deliverable and i simply want this cell/column to display the next date that the item is due based on measuring todays date and the next upcoming date being the soonest out of all the of collected dates

I was testing something along the lines of the below and trying to get the earliest date out of the collected dates as long as that date is => today but couldnt get it to work.

Any help would be appreciated.

=MIN(COLLECT([Period 1]2, [Period 2]2, [Period 3]2, [Period 4]2, [Period 5]2, [Period 6]2, [Period 7]2, [Period 8]2, [Period 9]2, [Period 10]2, [Period 11]2, [Period 12]2, >TODAY))

• ✭✭✭✭✭✭
Options

Hi,

Try something like.

=MIN(COLLECT([Period 1]@row:[Period 3]@row; [Period 1]@row:[Period 3]@row; @cell > TODAY()))

The same version but with the below changes for your and others convenience.

=MIN(COLLECT([Period 1]@row:[Period 3]@row, [Period 1]@row:[Period 3]@row, @cell > TODAY()))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

That worked! Thank you!

• ✭✭✭✭✭✭
Options

Excellent!

Happy to help!

Best,

Andrée

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

Just to expand on this a bit. What happens if i wanted to incorporate the checkbox next to each period as a variable for if that period should be included in the collect statement?

In other words, is there a way we can collect all the dates based on their status ie only collect the period dates if they are unchecked within the checkbox column next to each period? Just curious if this is possible.

• ✭✭✭✭✭✭
Options

Better late than never!

Try something like this.

=MIN(COLLECT([Period 1]@row:[Period 3]@row, [Period 1]@row:[Period 3]@row, @cell > TODAY(), [Box1]@row:[Box3]@row, @cell = 1))

The same version but with the below changes for your and others convenience.

=MIN(COLLECT([Period 1]@row:[Period 3]@row. [Period 1]@row:[Period 3]@row. @cell > TODAY(). [Box1]@row:[Box3]@row. @cell = 1))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

I can't edit the previous post.

The first one would collect if the checkbox is checked and this one (the correct one) will collect if unchecked.

=MIN(COLLECT([Period 1]@row:[Period 3]@row, [Period 1]@row:[Period 3]@row, @cell > TODAY(), [Box1]@row:[Box3]@row, @cell = 0))

The same version but with the below changes for your and others convenience.

=MIN(COLLECT([Period 1]@row:[Period 3]@row. [Period 1]@row:[Period 3]@row. @cell > TODAY(). [Box1]@row:[Box3]@row. @cell = 0))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!