Set duration for month automatically

Get Help
1

Hi,

I have build an automation where every 12 months a new year is being created. After a year is being created, months are created. For these months I would like to automatically set the duration.

The duration for every month is known when created, except for february.

  • January is always Jan 1 - Jan 31 + ToText(Year(Today()) + 1)
    Is there a way to create a formula like this to set the duration?

  • February is slightly different. Start date is always known → Feb 1.
    The end date is either Feb 28th or Febr 29th.

    • We know that every 4 year the result should be Feb 29th + ToText(Year(Today()) + 1).
      To know when this occurs we can divide the year by 4. If the result is a whole number, then February has 29 days. Otherwhise February has 28 days.

Is there a solution for this?

I hope it is clear what I am trying to achieve.

Thanks!

2

The easiest way to get the last day of any given month is to get the first day of the next month, and then subtract a day.
So to get the number of the last day of February next year, try something like this:

Day(Date(Year(Today()) + 1,3,1) - Days(1))

Explanation:
Date(Year(Today()) + 1,3,1) will get you 1st March 2024,
Date(Year(Today()) + 1,3,1) - Days(1) will get you the previous day (e.g. 29th February 2024)
Day(Date(Year(Today()) + 1,3,1) - Days(1)) gets you the number 29

3

You may actually find that your formulas for other months could be simplified as well, using the same principle, so you wouldn’t need to ‘hard-code’ any specific number of days in the month.

4

Thanks @Chr1sG ,

This is better than I was hoping for! One last question in addition to the above.

With those dates you have either a start datum or an end date of a month. Is there a way to combine those to into one single date field with start and end date?

Thanks!

5

You can construct a date range using the formula DateRange(Start, End)
where Start and End are Date values (from fields, or from formulas themselves)

6

Yes, this does the work! I was trying to set a date field instead of using a formula field. Thanks!