Count of prior month dates

I am using the following formula to count the dates in this row that are equal to the current month. However, I am trying to make adjustments to this formula to count dates that appear in the prior month and keep getting errors. I believe part of my challenge is accounting for the end of the year.

=COUNTIFS([First Date]@row:[Eighth Date]@row, >=DATE(YEAR(TODAY()), MONTH(TODAY()), 1), [First Date]@row:[Eighth Date]@row, <IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)))

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

Hey Michelle

When I do this I use an IF statement to look for January.

=IF(MONTH(TODAY())=1,COUNTIFS([First Date]@row:[Eighth Date]@row, ISDATE(@cell), [First Date]@row:[Eighth Date]@row, YEAR(TODAY())-1, [First Date]@row:[Eighth Date]@row, MONTH(@cell)=12), COUNTIFS([First Date]@row:[Eighth Date]@row, ISDATE(@cell), [First Date]@row:[Eighth Date]@row, YEAR(TODAY()), [First Date]@row:[Eighth Date]@row, MONTH(@cell)=MONTH(TODAY())-1)

Does this work for you?

Kelly

All comments

Jeff Reisman

@Michelle M

Counting data from the previous month in January is always difficult to automate. You might try modifying the TODAY functions to find the YEAR and MONTH for TODAY(-31). That will almost always give you the year and day of the previous month, except on a few 1st days of the month.

Kelly Moore

Hey Michelle

When I do this I use an IF statement to look for January.

Does this work for you?

Kelly

Michelle M

Thank you! This is very helpful.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317215#Comment_317215

I didn't even think about just giving it different parameters for January within the same formula. Nice approach!

Paul Newcome

I generally try to nest the IF statements inside of the COUNTIFS because the IF statements are (usually) shorter than the COUNTIFS which will save you some keystrokes.

=COUNTIFS([First Date]@row:[Eighth Date]@row, AND(@cell< DATE(YEAR(TODAY()), MONTH(TODAY()), 1), @cell>= DATE(YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1), IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), 1)))

=COUNTIFS([First Date]@row:[Eighth Date]@row, AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1), IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1)))

The first establishes a date range of less than the first of the current month and greater than or equal to the first of the previous month. The second one is a YEAR and MONTH comparison as opposed to a date range. Both show how replicating the shorter portion of the formula can shorten things quite a bit even with adding in the additional AND function in both and IFERROR functions in the second.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.