Formula that displays the year/month, but the month starts on the Monday before the 1st

Question

I'm thinking that there would be a formula to calculate all the days in this pattern described below. But, I just can't seem to crack it.

I have a scheduling base that I would like to group by month. But the beginning of this month grouping would need to start on the Monday that is on or before the 1st of the month. This way the grouping includes all 7 days of the 1st week. The monthly grouping would end on the last Sunday of the Month to include all days of the last week.

Example: For June 2023... Jun 1st is on a Thursday. So the Grouping of June 2023 would start on the Monday before, May 29th. The grouping would include all rows from May 29th thru the last Sunday of the Month June 25th.

Then the next grouping would start. The July 2023 grouping would include all dates from Monday Jun 26th until Sunday July 30th, and so on...

The formula would label each row "Year Month#: Month Name" or "2023 06: June". This keeps the groups in chronological order.

Thanks in advance if anyone has suggestions!

joshsorenson · Accepted Answer

To accomplish this task in Airtable, you can generate a formula field that computes the initiation of customized monthly groupings. Afterwards, create another formula field to mark the grouping, using the following steps:

1. Create a new formula field named "Group Start Date".

2. Use the following formula in the "Group Start Date" field:

IF(
WEEKDAY({Date}) = 1,
{Date},
DATEADD(
{Date},
IF(WEEKDAY({Date}) = 0, -6, 2 - WEEKDAY({Date})),
'days'
)
)

To determine if the date falls on a Monday (1), this formula is used. If the date is on a Monday, it remains unchanged. If it is not, the formula locates the previous Monday.

3. Create a new formula field named "Month Grouping".

4. Use the following formula in the "Month Grouping" field:

DATETIME_FORMAT(
{Group Start Date},
"YYYY MM: MMMM"
)

With this formula, you can easily format the "Group Start Date" to display as "Year Month#: Month Name." This will create a "Month Grouping" field which allows you to organize your records based on the customized month groupings you have set.

This is great! Thank you!!

Matt_Grzan · Answer