# Start date of the month for the previous 13 months rolling

Options
✭✭

I wanted to have a rolling 13 month table that and hoped to have a formula for the first date of the month for 13 months. I have a helper column with today's date in it and have listed the months and the start date as per the attached. Is there a formula that I can put in the start date column to have this roll on from one month to the next so it's only showing 13 months from the current month back?

Tags:

• ✭✭✭✭✭✭
Options

Hi @B Young

You can use two formulas to achieve the result you need

Formula 1 next to current month - =DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

Having the current date adjust is the key, you then add the second formula against month 1 and drag it upwards against the rest of the months.

To the first cell

The formula reads the cell below basically taking 1 away from the month and accounting for the Dec - Jan transition by taking 1 away from the year and making the month 12 in that case.

=IF(MONTH([Start Date]13) = 1, DATE(YEAR([Start Date]13) - 1, 12, 1), DATE(YEAR([Start Date]13), MONTH([Start Date]13) - 1, 1))

Hope that helps

Thanks Paul

• ✭✭✭✭✭✭
Options

Hi @B Young

You can use two formulas to achieve the result you need

Formula 1 next to current month - =DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

Having the current date adjust is the key, you then add the second formula against month 1 and drag it upwards against the rest of the months.

To the first cell

The formula reads the cell below basically taking 1 away from the month and accounting for the Dec - Jan transition by taking 1 away from the year and making the month 12 in that case.

=IF(MONTH([Start Date]13) = 1, DATE(YEAR([Start Date]13) - 1, 12, 1), DATE(YEAR([Start Date]13), MONTH([Start Date]13) - 1, 1))

Hope that helps

Thanks Paul

• ✭✭
Options