TODAY() Function stopped working

I have the following formula for determining if an item was used in the last month. Example: between 12/21/2022, and 01/21/2023.
=IF(AND([Date Entered]@row < DATE(YEAR(TODAY()), MONTH(TODAY()), 21), [Date Entered]@row >= DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 21)), 1, 0)
At some point it has stopped working. It was in a checkbox column and determined if items were automatically sent to finance or not.
Does anyone know why it stopped working, or how to fix it?
Thanks
Best Answer
-
It is because of the bold portion:
=IF(AND([Date Entered]@row < DATE(YEAR(TODAY()), MONTH(TODAY()), 21), [Date Entered]@row >= DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 21)), 1, 0)
1 minus 1 equals 0. There is no month zero. Try this:
=IF(AND([Date Entered]@row < DATE(YEAR(TODAY()), MONTH(TODAY()), 21), [Date Entered]@row >= DATE(YEAR(TODAY()), IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), 21)), 1, 0)
Answers
-
It is because of the bold portion:
=IF(AND([Date Entered]@row < DATE(YEAR(TODAY()), MONTH(TODAY()), 21), [Date Entered]@row >= DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 21)), 1, 0)
1 minus 1 equals 0. There is no month zero. Try this:
=IF(AND([Date Entered]@row < DATE(YEAR(TODAY()), MONTH(TODAY()), 21), [Date Entered]@row >= DATE(YEAR(TODAY()), IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), 21)), 1, 0)
Help Article Resources
Categories
Check out the Formula Handbook template!