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)))
Best Answer
-
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
Answers
-
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.
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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!
-
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
-
Thank you! This is very helpful.
-
I didn't even think about just giving it different parameters for January within the same formula. Nice approach!
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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 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.
