Next deliverable date

Bryan jones
edited 12/09/19 in Smartsheet Basics

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.

Comments

  • Schiff A.
    Schiff A. Employee

    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

  • Thank you so much!!! that worked perfectly.

    Really appreciate the help.

  • ateleoh
    ateleoh ✭✭

    Hello, I am still looking for a solution.  Can anyone help?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    What do you need help with?

    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.