nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Adding Months to the Same Column

S.Stone

I am trying to setup a sheet that can project dates decades into the future. I want a formula that can look the date in the row above, check to see if it's annual or quarterly, then add the appropriate number of months to each row.

While testing the formulas, I keep getting stuck on how to add one year (12 months) to each row. The simplest formula adds days, which causes problems with leap years.

Ideally, I'd like to keep the starting date in Row 1 (07/01/25) and just add the 12 months (annual) or 3 months (quarterly) to each row. But before I can add the IF arguments, I need to get the base formula working.

Any formula help is appreciated!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Here would be something for 3 months:

=IFERROR(DATE(YEAR(Date5), MONTH(Date5) + 3, DAY(Date5)), DATE(YEAR(Date5) + 1, MONTH(Date5) - 9, DAY(Date5)))

=IF(Type1 = "Annual", DATE(YEAR(Date5) + 1, MONTH(Date5), DAY(Date5)), IFERROR(DATE(YEAR(Date5), MONTH(Date5) + 3, DAY(Date5)), DATE(YEAR(Date5) + 1, MONTH(Date5) - 9, DAY(Date5))))

Paul Newcome

This should work assuming you have a Sheet Summary field labeled "Type" where you find Annual vs Quarterly, and a Sheet Summary field (date type) called "Starting Date" where you have the start date listed.

=IF(Type# = "Annual", DATE(YEAR([Starting Date]#) + (Term@row - 1), MONTH([Starting Date]#), DAY([Starting Date]#)), IF(Type# = "Quarterly", DATE(YEAR([Starting Date]#) + (FLOOR(MONTH([Starting Date]#) + (((Term@row - 1) * 3) - 1), 12) / 12), IF(MOD(MONTH([Starting Date]#) + ((Term@row - 1) * 3), 12) = 0, 12, MOD(MONTH([Starting Date]#) + ((Term@row - 1) * 3), 12)) + IF(IF(MOD(MONTH([Starting Date]#) + ((Term@row - 1) * 3), 12) = 0, 12, MOD(MONTH([Starting Date]#) + ((Term@row - 1) * 3), 12)) < 0, 12, 0), DAY([Starting Date]#))

All comments

Darren Mullen

@S.Stone You can construct the date and keep what you want from the previous date, and change what you need using the DATE() function.

In your example:

=DATE(year(Date5)+1, month(Date5), day(Date5))

That will get a date exactly 1 year from the date that is in the Date5 column

S.Stone

Thanks, @Darren Mullen. The year works, but I'm having trouble with the months using that same setup. I was hoping to add 3 or 12 months so I could easily write the IF formula for annual or quarterly adjustments.

Any thoughts to address months?

Darren Mullen

@S.Stone Yes, you'd have to add 3 months to the month, and if the value is greater than 12, you'd have logic that takes a value like "13" and converts it to 1 while also adding 1 to the year.

In summary, if you are adding months instead of years, you have to see if the result of adding 3 months is in the same year, or i

Have done this more than once. Just takes some time to write it out.

S.Stone

Great, let me know if you're able to write out an example!

Paul Newcome

Here would be something for 3 months:

=IFERROR(DATE(YEAR(Date5), MONTH(Date5) + 3, DAY(Date5)), DATE(YEAR(Date5) + 1, MONTH(Date5) - 9, DAY(Date5)))

=IF(Type1 = "Annual", DATE(YEAR(Date5) + 1, MONTH(Date5), DAY(Date5)), IFERROR(DATE(YEAR(Date5), MONTH(Date5) + 3, DAY(Date5)), DATE(YEAR(Date5) + 1, MONTH(Date5) - 9, DAY(Date5))))

S.Stone

@Paul Newcome thanks so much! It's working perfectly now!

Paul Newcome

Happy to help. 👍️

We could even turn this into a column formula if you are able to use a Sheet Summary field for the starting date and type if you're interested.

S.Stone

Could I use the sheet summary to have it talk to another sheet using cross-sheet references? The date and annual/quarterly references are linked in from another sheet (the "main database") and it's going to link out to a bunch of these mini sheets to list the core data for each project. I'm all for a column formula as long as the functionality works in the overall "ecosystem" of the solution.

Paul Newcome

You can certainly have formulas containing cross sheet references in a Sheet Summary field. I do this frequently in Control Center builds to make certain metadata points available in metrics fields by using single cell cross sheet references.

=INDEX({XSheet Reference To Single Cell}, 1)

This allows me to surface things like a project number in my metrics sheet without having a full column formula dedicated to it (helps sheet performance).

Paul Newcome

S.Stone

Thanks so much! I'll test it out tomorrow.

S.Stone

Good morning, Paul,

I used your tips to cross-link the two values I needed from the main sheet, cutting out a column and turning nearly everything in this sheet into a column formula. Working like a charm!

Thanks again!

Paul Newcome

Happy to help. 👍️

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.