How to show next occurrence? (Dates, Recurring meetings)

Options

Hi all,

It's early in the morning and I can't get my brain to figure this out.

I have a number of different recurring meetings and I would like to generate a report that shows the next upcoming occurrence of each.

So let's say data looks like this:

I would like to generate a report that shows me something like:

Board 09/09/20

CMSC 09/09/20

FAC 16/09/20

OC 07/10/20


I currently have a report that shows me all upcoming meetings within the next X days, but this means that for some meetings show up multiple times because they happen more frequently, whereas meetings that happen less often (eg quarterly) don't show up until closer to when they are happening.

Is there a way to get the next occurrence of a lookup value vs today's date?


TIA

Answers

  • Stefan
    Stefan ✭✭✭✭✭✭
    Options

    Hi @Liz Lomax ,

    in the report, define your sorting to be primary by date and secondary by meeting type. In the "When" section of the report you can set the date has to be within the next xxx days. This should limit the amount of (double) entries.

    Hope this helps.

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    Options

    Hi @Liz Lomax

    I would suggest you to use an helper column checkbox type column, that can be hidden, with this formula:

    =IF(MIN(COLLECT([Date Column]:[Date Column], [Team Column]:[Team Column], [Team Column]@row))=[Date Column]@row, 1, 0)

    If the Date is the closest one, the box will be checked.

    Then in your report just displays rows where the box is checked.

    Hope it helped!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!