#invalid value error on a counting from previous year month

Raul Cabrera

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

Jeff Reisman

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.

Jeff Reisman

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

Jeff Reisman

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.

Raul Cabrera

@Jeff Reisman where should i input the -31 into the formula?

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

Jeff Reisman

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

Raul Cabrera

Thanks @Jeff Reisman i fixed the formula to a simple one and add the -31 and it works thanks

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

I left here this formula as reference for the ones who needs.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/316523#Comment_316523

Happy I could help!