Help with Dates not spanning over days that are not being used.

Genesia

So, as you can see there in the calendar view I have this meeting that occurs every Monthly Monday, but the end date for this meeting stops on 1/25/24. The issue that I'm having is how can I have this meeting show up only on Mondays every month until that end date. Also, I have a formula that is set up, and I have recurring monthly meetings that don't have an end date. So that means that every month this meeting is going to be copied on the calendar, and I will have to go in and delete it manually. Any help with suffice. If i need to give further details please let me know.

Paul Newcome

Regardless of how the rows get added, you definitely need separate rows for each Monday (for example).

There MIGHT be a way to set up an automation that is a little bit convoluted but should work in theory depending on how new events are added and whether or not you can standardize the frequency (set list of numbers in one dropdown and durations such as days / weeks / months in another column) as well as overall duration number of repetitions.

Again... This MIGHT work. It should in theory, but I want to make sure you are able to standardize a few things before getting into the details of a complex build.

Samuel Mueller

@Genesia you would set up one automation something like this

It would look something like this. But we would probably need some helper columns to get it to populate the Next month, I'm guessing that if it populated the Monday of the meeting, that would be too late. So alternatively, I would maybe make this automation the last Monday of the Monday, then a helper column looking at that date+7, to get the next Monday (first Monday of the month). Then on the last Monday of every month, the row would update to the next months first Monday.

If your row doesn't change, this would work forever.

Samuel Mueller

I seem to run into a similar problem pretty often. If you use a match formula against an autorow column, you can get the row number, then you would be able to index duplicate rows (like duplicate row 1, duplicate row 2) in order and maybe create a formula from that to get the Monday over the next 5 or whatever meetings, based on the first Monday. Then based on the formula it would occur for every row added with the same "Name"

I think no matter what, it would get pretty complicated 😂

Samuel Mueller

@Genesia maybe you could make the start date and the end date the same, and then use an automation to update the dates each month after the meeting occurs.

I don't think there is a, I want to see this meeting every Monday option like you have in outlook.

The other option would be like you said to have a row for each Monday in your sheet.

Paul Newcome

You would have to have separate rows for each date.

Genesia

@Samuel Mueller, to Clarify for instance I would do on a row 9/21/23 start date and end date would be 9/21/23. For that particular row I would set up an automation to recur weekly until the end date.

Since we will have multiple of these examples, does that mean that i would need to set up an automation every time i received one like this.

Genesia

@Paul Newcome , I did present this idea that we would have to do a row for each date, she didn't like that idea, she wanted an automation to be set up, something that would be easier on the team. I personally can't think of a better way, because not everyone has the same access as i do to create automations. So My only options i think would be to set up an automation for that particular row or we have to create a new row for each monday until it ends correct? Like there is no way i can just create an automation based off of one scenario, when the current automation i have set up is recur based on the frequency of the meetings.

Paul Newcome

Regardless of how the rows get added, you definitely need separate rows for each Monday (for example).

There MIGHT be a way to set up an automation that is a little bit convoluted but should work in theory depending on how new events are added and whether or not you can standardize the frequency (set list of numbers in one dropdown and durations such as days / weeks / months in another column) as well as overall duration number of repetitions.

Again... This MIGHT work. It should in theory, but I want to make sure you are able to standardize a few things before getting into the details of a complex build.

Samuel Mueller

@Genesia you would set up one automation something like this

It would look something like this. But we would probably need some helper columns to get it to populate the Next month, I'm guessing that if it populated the Monday of the meeting, that would be too late. So alternatively, I would maybe make this automation the last Monday of the Monday, then a helper column looking at that date+7, to get the next Monday (first Monday of the month). Then on the last Monday of every month, the row would update to the next months first Monday.

If your row doesn't change, this would work forever.

Paul Newcome

@Samuel Mueller This works for populating the very next occurrence, but do you have any ideas on how to get it to populate multiple occurrences?

I have a solution I developed that can automatically populate multiple rows based on a single form submission that could be modified, but it would get pretty complicated. Wondering if you had any thoughts on it?

Samuel Mueller

I seem to run into a similar problem pretty often. If you use a match formula against an autorow column, you can get the row number, then you would be able to index duplicate rows (like duplicate row 1, duplicate row 2) in order and maybe create a formula from that to get the Monday over the next 5 or whatever meetings, based on the first Monday. Then based on the formula it would occur for every row added with the same "Name"

I think no matter what, it would get pretty complicated 😂

Samuel Mueller

Smartsheet could really use an EOMonth formula like excel has, where you can get the last day of the month, makes it a lot easier to create some of these date formula.s

Paul Newcome

@Samuel Mueller There is a solution for the lack of EOM function. Basically you output the first of the next month and then just subtract 1 day.

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1

Samuel Mueller

That gets you the previous month though. I want the end of day for next month. Which you can do with some formulas, just super complicated and nested, when an EOMONTH() would be fabulous and easy 😀

Paul Newcome

Next month (on a rolling basis) would be

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1

I also have an EDATE solution that works with either a negative or positive number of months that could be used similarly. It too uses an IFERROR(DATE(), DATE()) combo, so it would be a matter of referencing the number of months column and then adding 1 to it to get the last day of the month output by the EDATE solution (or a helper column that just adds 1 to the number of months and reference that instead of the "display" number of months).

Samuel Mueller

Oh love that, that is a lot easier. I still wish they had the function though 😂

Paul Newcome

Oh I agree. The function would be great to have and much easier to use. Typically if I am not going over 12 months, I will use the first formula with slight modification.

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 11, 1)) - 1

When using the above, you can use any number of months (within 12) so long as you keep a few rules in mind.

The operator in the second year portion will be the same as the operator in the first month portion.

The operator for the second month portion will be opposite of the first.

The number for the second month portion will add to the number in the first to equal 12.

+1 month / +1 year / -11 months

+2 months / +1 year / -10 months

-3 months / -1 year / +9 months

-4 months / -1 year / +8 months

As for the EDATE solution... I tested it back to plus and minus 50 months with no issues. It should be out there in the Community somewhere if you do a search for "EDATE".