Formula to calculate 30 days before

Hi, I have a column with a Partner Due Date (manually entered), and a column with an Internal Due Date (column formula). I would like for the Internal Due Date to be 30 days before the Partner Due Date and am using this formula:

=DATE(YEAR([Partner Due Date]@row), MONTH([Partner Due Date]@row) - 1, DAY([Partner Due Date]@row))

For any Partner Due Dates that are January, I am getting an #INVALID VALUE error. Any suggestions for how to fix this formula so that January Internal Due Dates are December? Thank you!

Find more posts tagged with

Accepted answers

Kelly P.

@alys If you literally want the internal due date to be 30 days prior to the partner due date, then you can just use:

=[Partner Due Date]@row - 30

However, it looks like what you really want is the same day of the month, but one month prior. If so, try this:

=IFERROR(IF(MONTH([Partner Due Date]@row) > 1, DATE(YEAR([Partner Due Date]@row), MONTH([Partner Due Date]@row) - 1, DAY([Partner Due Date]@row)), IF(MONTH([Partner Due Date]@row) = 1, DATE(YEAR([Partner Due Date]@row) - 1, MONTH([Partner Due Date]@row) + 11, DAY([Partner Due Date]@row)))), "")

The IFERROR is there just in case there are any blank partner due dates.

All comments

Kelly P.

@alys If you literally want the internal due date to be 30 days prior to the partner due date, then you can just use:

=[Partner Due Date]@row - 30

However, it looks like what you really want is the same day of the month, but one month prior. If so, try this:

The IFERROR is there just in case there are any blank partner due dates.

alys

@Kelly P. thank you!

Kelly P.

@alys 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.