#invalid value error on a counting from previous year month

Options

Hi good day everyone. I have a formula that was working fine until now January. The formula what is doing is counting all new items added in a sheet on previous month

=COUNTIFS({What is it?}, "Risk", {Created date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY(), 1) - 1), IFERROR(YEAR(@cell), 0 = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY(), 1) - 1))))))

I am just guessing but can be this for the year change? any suggestion to fix this?

as you see below the current month count is working fine but the previous month counting is giving the error


Tags:

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    You could calculate your YEAR value by finding the year from TODAY(-31). That will find you the year from last month no matter what day of the current month it is.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    I'm not totally clear on what you're doing here, but it seems like wherever you're calculating a YEAR value for the previous month, using TODAY(-31) in place of TODAY() will get you the correct year, each and every January.

    =COUNTIFS({What is it?}, "Risk", {Created date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY(-31)), MONTH(TODAY(), 1) - 1), IFERROR(YEAR(@cell), 0 = YEAR(DATE(YEAR(TODAY(-31)), MONTH(TODAY(), 1) - 1))))))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!