I am attempting to use a date in a column to calculate 6 months after that date into another column

My formula works as long as it is in the original date is in the first 6 months of the year, but if it is in the last half of the year, my formula is not looping to the next year.

Example: I need 6 months from 08/01/2022 which would be February of 2023

Here is the formula I have been using: =DATE(YEAR([Approval Date]1), MONTH([Approval Date]1) + 6, DAY([Approval Date]1))

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Tyr this:

=IFERROR(DATE(YEAR([Approval Date]1), MONTH([Approval Date]1) + 6, DAY([Approval Date]1)), DATE(YEAR([Approval Date]1) + 1, MONTH([Approval Date]1) - 6, DAY([Approval Date]1)))

Lorraine Stevens

Hi @Paul Newcome ! I was searching for this exact formula and came across your comment. I used it and it worked beautifully to calculate the date 6 months in the future. I then tried to convert it to a column formula and received the message "the column formula syntax isn't quite right, see our help article...(regarding column formulas)". The column that I am using in my formula is the auto created date. Do you think that is the reason?

Thank you so much for the help!

Genevieve P.

Hi @Lorraine Stevens

Column Formulas need to have generic row references so it can apply to every row. In the formula above, the cell reference has a 1 next to it, indicating the cell is in row 1. Instead, replace this with @row!

=IFERROR(DATE(YEAR([Approval Date]@row), MONTH([Approval Date]@row) + 6, DAY([Approval Date]@row)), DATE(YEAR([Approval Date]@row) + 1, MONTH([Approval Date]@row) - 6, DAY([Approval Date]@row)))

Let me know if that works now!

Cheers,

Genevieve

Lorraine Stevens

@Genevieve P. Oh man, can't believe I didn't see that!! Thank you!

Genevieve P.

No problem at all 🙂

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.