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!

Find more posts tagged with

Accepted answers

Leibel S

@Tina Ciak

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)

All comments

Paul Newcome

Are you able to provide some screenshots for reference?

Tina Ciak

Leibel S

@Tina Ciak

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)

Tina Ciak

@Leibel S

This works REALLY well.

Thank you!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.