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

Ami Veltrie

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))

Toufong Vang

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.

Toufong Vang

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.

Ami Veltrie

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