Next deliverable date based on TODAY

msupplied

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

Andrée Starå

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

msupplied

That worked! Thank you!

Andrée Starå

Excellent!

Happy to help!

Best,

Andrée

msupplied

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.

Andrée Starå

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?

Andrée Starå

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?