# Auto update a month with a daterange

**URL:** <https://community.fibery.io/t/auto-update-a-month-with-a-daterange/3787>\
**Category:** Misc\
**Created:** [January 9, 2023, 9:21am UTC](https://community.fibery.io/t/auto-update-a-month-with-a-daterange/3787 "2023-01-09T09:21:58Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Marloes](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/marloes/32/5926_2.png) [@Marloes](https://community.fibery.io/u/Marloes)\
**Post date:** [January 9, 2023, 9:21am UTC](https://community.fibery.io/t/auto-update-a-month-with-a-daterange/3787/1 "2023-01-09T09:21:58Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![Marloes](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/marloes/32/5926_2.png) [@Marloes](https://community.fibery.io/u/Marloes)\
**Post date:** [January 9, 2023, 9:28am UTC](https://community.fibery.io/t/auto-update-a-month-with-a-daterange/3787/2 "2023-01-09T09:28:14Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/e/e9765baa72eadf5b69d05ece7c25158c5a8cd3ce.png)

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [January 9, 2023, 9:50am UTC](https://community.fibery.io/t/auto-update-a-month-with-a-daterange/3787/3 "2023-01-09T09:50:58Z")

</div>

> [@Marloes](#):
>
> I’m getting the notification that the automation failed to execute, because such formula is not supported at the moment.

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:

```auto
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())
  )
)

```

---

<div class="post-metadata">

**Author:** ![Marloes](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/marloes/32/5926_2.png) [@Marloes](https://community.fibery.io/u/Marloes)\
**Post date:** [January 9, 2023, 10:03am UTC](https://community.fibery.io/t/auto-update-a-month-with-a-daterange/3787/4 "2023-01-09T10:03:15Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [January 9, 2023, 10:05am UTC](https://community.fibery.io/t/auto-update-a-month-with-a-daterange/3787/5 "2023-01-09T10:05:07Z")

</div>

Let me check and get back to you

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [January 9, 2023, 10:18am UTC](https://community.fibery.io/t/auto-update-a-month-with-a-daterange/3787/6 "2023-01-09T10:18:00Z")

</div>

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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [May 1, 2023, 9:51am UTC](https://community.fibery.io/t/auto-update-a-month-with-a-daterange/3787/7 "2023-05-01T09:51:42Z")

</div>

Potentially useful:

> [@Beta testers wanted](https://community.fibery.io/t/beta-testers-wanted/4389):
>
> We may soon release an integration template that would allow users to create a database of time periods (days, weeks, months, quarters, years) as the next step in making [managing date information](https://community.fibery.io/t/template-for-date-grouping/2678) in Fibery more user-friendly. We’re interested in getting feedback from users on the first iteration before releasing to a larger audience. If you’re interested in trying out what we have so far, here’s what you need to do: Go to the integration tab in any space (I suggest creating a new space for e…