Start date of the month for the previous 13 months rolling

B Young

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?

Start Date Formula.png

Paul McGuinness

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))

*I've adjusted the formula to reflect your column name 'Start Date'

Hope that helps

Thanks Paul

Paul McGuinness

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))

*I've adjusted the formula to reflect your column name 'Start Date'

Hope that helps

Thanks Paul

B Young

Thank you - this answers the question. Appreciate your help.