Auto update a month with a daterange

How can I automatically update a month within a date range every single month. I’m using the following formula in my on schedule automation.

DateRange(
Date(
Year([Step 1 Periodes].[When periode].Start()),
Month([Step 1 Periodes].[When periode].Start()) + 1,
Day([Step 1 Periodes].[When periode].Start())
),
Date(
Year([Step 1 Periodes].[When periode].End()),
Month([Step 1 Periodes].[When periode].End()) + 1,
Day([Step 1 Periodes].[When periode].End()))
)

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.

Thank in advance!

As an adition.

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.

I suggest using a construction like this:

DateRange(
  Date(
    If(Month([Step 1 Periodes].[When periode].Start())=12,
      Year([Step 1 Periodes].[When periode].Start())+1,
      Year([Step 1 Periodes].[When periode].Start())
    ),
    If(Month([Step 1 Periodes].[When periode].Start())=12,
      1,
      Month([Step 1 Periodes].[When periode].Start()) + 1
    ),
    Day([Step 1 Periodes].[When periode].Start())
  ),
  Date(
    If(Month([Step 1 Periodes].[When periode].End())=12,
      Year([Step 1 Periodes].[When periode].End())+1,
      Year([Step 1 Periodes].[When periode].End())
    ),
    If(Month([Step 1 Periodes].[When periode].End())=12,
      1,
      Month([Step 1 Periodes].[When periode].End()) + 1
    ),
    Day([Step 1 Periodes].[When periode].End())
  )
)

Hi Chris,

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?

Let me check and get back to you

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:

DateRange(
  Date(
    If(Month([Step 1 Periodes].[When periode].Start())=12,
      Year([Step 1 Periodes].[When periode].Start())+1,
      Year([Step 1 Periodes].[When periode].Start())
    ),
    If(Month([Step 1 Periodes].[When periode].Start())=12,
      1,
      Month([Step 1 Periodes].[When periode].Start()) + 1
    ),
    1
  ),
  Date(
    If(Month([Step 1 Periodes].[When periode].End())>=11,
      Year([Step 1 Periodes].[When periode].End())+1,
      Year([Step 1 Periodes].[When periode].End())
    ),
    If(Month([Step 1 Periodes].[When periode].End())>=11,
      Month([Step 1 Periodes].[When periode].End()) - 10,
      Month([Step 1 Periodes].[When periode].End()) + 2
    ),
    1
  )
  - Days(1)
)

Hope that makes sense.

3 Likes