But unfortunately I’m getting the notification that the automation failed to execute, because such formula is not supported at the moment.
Extra note: We have to be aware that if the date changes from december to january the year should also be updated. Maybe this happens automatically, but we should be aware of this.
I don’t think this solution will work when a month or year changes. I have the same automation for updating the date range for this week. It ran perfect, up untill the moment there should be a month change.
Below you see This week and next week. I ran the automation manually, but it failed at this point.
If the current month is December, then the value of Month([Step 1 Periodes].[When periode].Start()) + 1 is 13, which is an invalid value to pass to the Date() function.
Thanks for your reply. It seems like the formula doesn’t recognize the + 1 options when referring to months (or days when the month is changing). So the whole formula setup is not working (even when the formula is correct).
Maybe it’s because its not described as a month number but as a month name?
Ah, I think the problem is unrelated to the Month bit - I think the problem is that Day([Step 1 Periodes].[When periode].End()) could be too large.
For example, if you’re trying to shift the date range 01/01/2023->31/01/2023 one month forward, the formula would give 01/02/2023->31/02/2023 but there is no such date as 31st February.
Here’s a quick tip/trick: if you need to get the last date of any given month, then just calculate the first date of the following month, and subtract 1 day.
In your case therefore, if you always want the first to the last date of the next month, you might want to try the following: