Help with Formula to Capture Dates for Full 12 Months
My team has a few use cases where we have savings projects in which the savings gets spread out over a 12 month period. To make the Smartsheet solution more sustainable, I would like to set the sheet up with a START DATE COLUMN and then only 1 Column per month, so JAN, FEB, MAR, etc, rather than a Mo/Year Column that will need to change over time.
I am currently trying to use this formula:
=IF(MONTH([Start Date]@row) = 2, [Start Date]@row, IF(MONTH(JAN@row) = 12, DATE(YEAR(JAN@row) + 1, 1, DAY(JAN@row)), DATE(YEAR(JAN@row), MONTH(JAN@row) + 1, DAY(JAN@row))))
But when I add this to the last of the 12 month columns, I get a #CIRCULAR REFERENCE error message. Is there any way around this? Really hoping I can make this work.
Thank you!
Best Answer
-
Make 3 helper columns that simplify your formula:
- Start Year: =YEAR([Start Date]@row)
- Start Month =MONTH([Start Date]@row)
- Start Day =DAY([Start Date]@row)
then your formula for each of the months is the below, replacing the bolded numbers with the corresponding month (e.g. April would be 4)
=DATE(IF([Start Month]@row > 1, [Start Year]@row + 1, [Start Year]@row), 1, [Start Day]@row)
Answers
-
Are you able to provide some screenshots for reference?
-
-
Make 3 helper columns that simplify your formula:
- Start Year: =YEAR([Start Date]@row)
- Start Month =MONTH([Start Date]@row)
- Start Day =DAY([Start Date]@row)
then your formula for each of the months is the below, replacing the bolded numbers with the corresponding month (e.g. April would be 4)
=DATE(IF([Start Month]@row > 1, [Start Year]@row + 1, [Start Year]@row), 1, [Start Day]@row)
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!