#invalid value error on a counting from previous year month
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
Best Answers
-
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!
-
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
-
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 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))))))
-
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!
-
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.
-
Happy I could help!
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
