Need help with date reporting formula

I have 4 formulas in seperate cells that look at open and closed actions and does a count of how many there are for those months. The current formula for the current month:-

=COUNTIFS({Date opened}, >=DATE(YEAR(TODAY()), MONTH(TODAY()), 1), {Date opened}, <DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1))

works fine and has done all the time.

The formulas to count for previous months worked fine with no issues prior to the new year and now they are all coming back with #Invalid Vaule and i am not sure what has broken over the new years or how to fix the issue. The formula to count for previous months is:

=COUNTIFS({Date opened}, >=DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), {Date opened}, <DATE(YEAR(TODAY()), MONTH(TODAY()), 1))

I increased the -1 value to then show for 1 month ago, -2 for 2 months ago etc. I am assuming that this has something to do with how smartsheets reports the dates and it being Jan it cannot go before Jan. I have tried to adjust the year to -1 and the get #unparseable so not sure how to fix so that i can get this to work. Any help would be greatly appreciated.

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

The same thing should have actually been happening last month for counting current month because of the MONTH(TODAY()) + 1 piece.

They both should fail for pretty much the same reason. Current month (January / 1) minus 1 is zero. There is no month zero. Similar logic for your formula calculating for the current month. If current month is December (12), 12 + 1 = 13, and there is no month 13.

The key here is an IFERROR to output another DATE. The thing to remember with this solution is that there are 12 months in a year.

IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 11, 1))

Notice the second DATE function subtracts 1 from the year and adds 11 to the month. Technically you could hard-code 12 here instead of adding 11, but adding 11 also shows a concept that can be used for things like -2 months, -3 months, so on and so forth.

When going backwards, the first DATE function subtracts the number of months and the second DATE function adds a number of months. Just make sure the two numbers add up to 12.

Similarly, if you want to look at next month, it would be +1 and -11. Two months into the future is +2 and -10, etc..

NealT

Hi Paul,

Thanks for the response. So to read this correctly i will need to nest the IFERROR formula you have provided within the COUNTIF so that it is able to read back to the previous year/month for what i am trying to achieve?

Paul Newcome

Yes. That is correct. Sorry about that. I got distracted part way through and didn't finish it out.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.