Auto populating meeting date

I am creating a policy database. Part of that database I have a submission form, committee members submit policies through this form when the policy is ready for the committee to review and vote on. I would like to auto populate the meeting date based on the submission or row certation. Eliminating manual work. Not sure if this is possible. We meet the 2nd Wednesday of every month. Ideally, I would like committee members to submit their documents 16 days before the meeting date. Any document submitted after that date it will roll over to the next month. For example. a policy is submitted between 12/26/2023 through January 29th will have a February 14th, 2024, date. Anything submitted January 30th through February 26th will have a March date. Is this possible?



Best Answer

  • brianschmidt
    brianschmidt ✭✭✭✭✭✭
    Answer ✓

    I think there's definitely a way to automate this more! It may just take some initial setup. Here's what I suggest...Because Smartsheet date formulas calculate based on day, month, and year as opposed to being based on days of the week (Mon, Tues, etc.), I think you need to start by...

    1. Create a separate sheet with a date column that has a series of meeting dates (one on each line). The downside here is you would have to keep adding the meeting dates once you've passed all the dates you added to the sheet.
    2. On the sheet referenced above, in your meeting review date, I would place the following as a column formula:

    =MIN(COLLECT({Meeting Dates}, {Meeting Dates}, >=(Created@row+16)))

    NOTE: the {Meeting Dates} is a cross sheet reference to your date column with all the meeting dates.

    This should return the closest future meeting date that's at least 16 days out from the date that row was created (aka date of form submission).

    Hope this helps!:)

Answers

  • brianschmidt
    brianschmidt ✭✭✭✭✭✭
    Answer ✓

    I think there's definitely a way to automate this more! It may just take some initial setup. Here's what I suggest...Because Smartsheet date formulas calculate based on day, month, and year as opposed to being based on days of the week (Mon, Tues, etc.), I think you need to start by...

    1. Create a separate sheet with a date column that has a series of meeting dates (one on each line). The downside here is you would have to keep adding the meeting dates once you've passed all the dates you added to the sheet.
    2. On the sheet referenced above, in your meeting review date, I would place the following as a column formula:

    =MIN(COLLECT({Meeting Dates}, {Meeting Dates}, >=(Created@row+16)))

    NOTE: the {Meeting Dates} is a cross sheet reference to your date column with all the meeting dates.

    This should return the closest future meeting date that's at least 16 days out from the date that row was created (aka date of form submission).

    Hope this helps!:)

  • @brianschmidt Thanks Brian. That worked. Until I can figure out a different method this will do!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!