Month Formula

Having some issues with the month formula popping up errors when being used as a reference for a range in the collect and countif/countifs functions

The below formula produces an error

=JOIN(COLLECT(Date:Date, Date:Date, MONTH(@cell) = 1))

The below formula returns a 1 even though every day in january is included in the Date column.

=Count(COLLECT(Date:Date, Date:Date, MONTH(@cell) = 1))

The simplest version of what I am trying to do is below, but it also produces an error

=countif(date:date,month(@cell)=1)

I've attached a picture below. Is anyone else having this issue? Anyone come up with a workaround?

Find more posts tagged with

Formulas

Comments

Paul Newcome

If you have any blanks or cells that do not have a date in them, you will get an error. To account for these cells, wrap the MONTH function in an IFERROR to generate a 0 (zero) for a cell that would otherwise throw an error. Because it is replacing the error with 0 and there is no month 0, these cells will no longer throw the error and will not be counted in the formula.

=JOIN(COLLECT(Date:Date, Date:Date, IFERROR(MONTH(@cell), 0) = 1))

=COUNTIFS(Date:Date, IFERROR(MONTH(@cell), 0) = 1)

L_123

kind of stupid that is needed... if the cell is blank the month isn't equal to 1. I would have never thought of that. Thanks

Paul Newcome

The error is coming from the MONTH function itself. The MONTH function is looking for a date. Referencing a blank cell is the same as saying

=MONTH()

or a cell with "abc"

=MONTH("abc")

Which obviously will not work.

With the MONTH function throwing an error, the formula as a whole will break and in turn throw an error.

My explanation using "Month 0" isn't entirely accurate.

The MONTH function produces a numerical value which is what we are comparing to the number 1. The IFERROR is telling the formula that if the MONTH function throws an error (as explained above), just produce the number 0.

Since 0 is not equal to 1 but is still a numerical value that can be compared to other numerical values, it allows the rest of the formula to continue working while excluding it from the count.

The reason I use the number 0 is because you will never look for month (or year for that matter) 0.

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?