Next deliverable date

Bryan jones

I have a list of deliverable dates and I would like to make the next deliverable date turn red, all dates after that remain white and any dates that occur in the past green.

I figured out how to get all past dates green but I can't figure out how to turn only the next date in the future to turn red without turning all the dates red or setting a range.

Any help is appreciated.  Thank you.

Schiff A.

Hello,

This can be accomplished by using a formula that looks at all dates that are today (relative to the day you're looking at the sheet) or in the future and then finds the earliest date in that range.  We can do this by using the COLLECT function which allows you to select a range of data based on certain criteria within the context of a formula.

To implement this in your sheet do the following:

1. Create a checkbox create a checkbox column called "Next Deliverable" with the following formula in every row: =IF([Deliverable Date]@row = MIN(COLLECT([Deliverable Date]:[Deliverable Date], [Deliverable Date]:[Deliverable Date], >=TODAY())), 1, 0)

Be sure to change the column reference if the column containing your deliverable dates is labeled differently.

This will check the box for the next upcoming deliverable date.  Note that if you have more than one item with the same deliverable date, both items would have the box checked when they are the next deliverable.

 

2. Set up conditional formatting to apply when the box is checked in the "Next Deliverable" column.

For more information about the COLLECT function see https://help.smartsheet.com/function/collect

Thanks!

Schiff

Smartsheet Support