Count of prior month dates

Michelle M · 01/24/22

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

Kelly Moore · 01/25/22

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

Jeff Reisman · 01/24/22

@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 · 01/25/22

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

Michelle M · 01/25/22

Thank you! This is very helpful.

Jeff Reisman · 01/25/22

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 · 03/04/22

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

or

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

Count of prior month dates

Best Answer

Answers

Categories