Auto populating meeting date

Rosetta Gordon

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?

brianschmidt

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

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

Rosetta Gordon

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