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))
Best Answer
-
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.
Answers
-
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 🤯 It worked! Thank you so much.
Help Article Resources
Categories
Check out the Formula Handbook template!
