Formula for checkbox when date is previous month (and considers previous year in Dec)

How can I alter this formula so that in December of each year, it only considers December of the previous year? As it stands, it's pulling in Dec '22 data. I only want Dec '23.

=IF([Payment Date]@row = "", "", IF(MONTH([Payment Date]@row) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), 1))

Find more posts tagged with

Formulas

Accepted answers

TVang

Hi, @Ami Veltrie , this will work for all dates:

=IFERROR(IF(((YEAR(TODAY()) - YEAR([Payment Date]@row)) * 12) + MONTH(TODAY()) - MONTH([Payment Date]@row) = 1, 1, ""), "")

The "previous month" = 1 month, so...

YEAR(01/05/2024) - YEAR(12/29/2023) = 1, times 12 = 12;

MONTH(01/05/2024) - MONTH(12/29/2023) = -11;

12 + (-11) = 1 .

For any payment date prior to December 2023, the expression, ((YEAR(TODAY()) - YEAR([Payment Date]@row)) * 12) + MONTH(TODAY()) - MONTH([Payment Date]@row) will return a value greater than 1. For any payment date this month or in the future, the expression will return 0 or a negative number.

When Payment Date is blank, the expression will throw an error. IFERROR() is used to catch it.

All comments

TVang

Hi, @Ami Veltrie , this will work for all dates:

=IFERROR(IF(((YEAR(TODAY()) - YEAR([Payment Date]@row)) * 12) + MONTH(TODAY()) - MONTH([Payment Date]@row) = 1, 1, ""), "")

The "previous month" = 1 month, so...

YEAR(01/05/2024) - YEAR(12/29/2023) = 1, times 12 = 12;

MONTH(01/05/2024) - MONTH(12/29/2023) = -11;

12 + (-11) = 1 .

When Payment Date is blank, the expression will throw an error. IFERROR() is used to catch it.

Ami Veltrie

@Toufong Vang 🤯 It worked! Thank you so much.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.