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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!