Month Formula

L_123
L_123 ✭✭✭✭✭✭
edited 12/09/19 inFormulas and Functions

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?

MonthError.JPG

Tags:

Comments

  • Paul Newcome
    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
    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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hi @Brittaney Pizzato<\/a> - You would match the max date against the date column, and you don't need Collect in this scenario, so it could read as =INDEX({Project Health}, MATCH(MAX({Date}), {Date}, 0))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":112211,"type":"question","name":"Automation Alert Row Info","excerpt":"Hi, I'm working in a workflow a I setup an alert. I run it and I notice that below my custom message, is all the information of the row that I modify. Is it possible to disable the information of the row being sent?","snippet":"Hi, I'm working in a workflow a I setup an alert. I run it and I notice that below my custom message, is all the information of the row that I modify. Is it possible to disable…","categoryID":322,"dateInserted":"2023-10-26T15:56:15+00:00","dateUpdated":null,"dateLastComment":"2023-10-26T16:07:33+00:00","insertUserID":169126,"insertUser":{"userID":169126,"name":"J.Sanabria","url":"https:\/\/community.smartsheet.com\/profile\/J.Sanabria","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T17:52:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":72271,"lastUser":{"userID":72271,"name":"Ella","url":"https:\/\/community.smartsheet.com\/profile\/Ella","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/981\/nUG9B5NDNGI7G.jpg","dateLastActive":"2023-10-26T18:05:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":16,"score":null,"hot":3396672828,"url":"https:\/\/community.smartsheet.com\/discussion\/112211\/automation-alert-row-info","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112211\/automation-alert-row-info","format":"Rich","lastPost":{"discussionID":112211,"commentID":401856,"name":"Re: Automation Alert Row Info","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401856#Comment_401856","dateInserted":"2023-10-26T16:07:33+00:00","insertUserID":72271,"insertUser":{"userID":72271,"name":"Ella","url":"https:\/\/community.smartsheet.com\/profile\/Ella","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/981\/nUG9B5NDNGI7G.jpg","dateLastActive":"2023-10-26T18:05:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-26T17:52:33+00:00","dateAnswered":"2023-10-26T16:07:33+00:00","acceptedAnswers":[{"commentID":401856,"body":"

@J.Sanabria<\/a> In the notification set up, under Message includes: you would have to select Message only.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":112203,"type":"question","name":"Help w\/ Sumif date is before today","excerpt":"Hi, Hitting a snag w\/ a very basic sumif. I need to sum the total capital investment column if it's corresponding date value falls before or on today's date. Here's the formula I have now: =SUMIF([Fiscal Month End]1:[Fiscal Month End]12, <=TODAY(), [Actual Capital Investment]1:[Actual Capital Investment]12) And a…","snippet":"Hi, Hitting a snag w\/ a very basic sumif. I need to sum the total capital investment column if it's corresponding date value falls before or on today's date. Here's the formula I…","categoryID":322,"dateInserted":"2023-10-26T14:19:00+00:00","dateUpdated":null,"dateLastComment":"2023-10-26T15:04:11+00:00","insertUserID":130047,"insertUser":{"userID":130047,"name":"Alex Hackford","url":"https:\/\/community.smartsheet.com\/profile\/Alex%20Hackford","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m_jVn4tpYLQ!zh1V8wNL1sQ!ziK_jPs8qoT","dateLastActive":"2023-10-26T16:09:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":130047,"lastUser":{"userID":130047,"name":"Alex Hackford","url":"https:\/\/community.smartsheet.com\/profile\/Alex%20Hackford","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m_jVn4tpYLQ!zh1V8wNL1sQ!ziK_jPs8qoT","dateLastActive":"2023-10-26T16:09:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":16,"score":null,"hot":3396663791,"url":"https:\/\/community.smartsheet.com\/discussion\/112203\/help-w-sumif-date-is-before-today","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112203\/help-w-sumif-date-is-before-today","format":"Rich","lastPost":{"discussionID":112203,"commentID":401837,"name":"Re: Help w\/ Sumif date is before today","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401837#Comment_401837","dateInserted":"2023-10-26T15:04:11+00:00","insertUserID":130047,"insertUser":{"userID":130047,"name":"Alex Hackford","url":"https:\/\/community.smartsheet.com\/profile\/Alex%20Hackford","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m_jVn4tpYLQ!zh1V8wNL1sQ!ziK_jPs8qoT","dateLastActive":"2023-10-26T16:09:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-26T15:04:18+00:00","dateAnswered":"2023-10-26T14:24:37+00:00","acceptedAnswers":[{"commentID":401817,"body":"

Your formula looks good! But is your \"Fiscal Month End\" formatted as a date column? It looks like a text (should have the leading 0's for the months). Double click on the header to change it to a Date type column and you should be good to go!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions