Start date of the month for the previous 13 months rolling
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?
Best Answer
-
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
Answers
-
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
-
Thank you - this answers the question. Appreciate your help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!