Calculating Number of Items in a Month based on System Generated date field

Andrea Mayer
Andrea Mayer ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hello all -

Needing help with a formula to calculate how many requests are submitted via a webform for each month. 

2 questions related to this:

1. Can a formula pull just the month from a System Generated cell: Created by (Date)?

2. Where are errors in this formula:

=COUNTIF({Date Submitted}, MONTH(@cell) = 1)

'Date Submitted' is the column name and I have tried it as a regular Date column and System Generated. 

Thank you!

Andrea

Comments

  • TeeM
    TeeM ✭✭✭

    Watching with interest smiley

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    1. Yes it can.

     

    2. There are no issues with the formula itself, but any blank cells within a range (including blank rows at the bottom of the sheet if the entire column is selected) will through an error for the MONTH function.

     

    To avoid this we can wrap the MONTH function in an IFERROR. This will allow us to designate a number that will essentially "replace" the missing month when a cell within the range does not have a date in it.

     

    Give this one a whirl...

     

    =COUNTIF({Date Submitted}, IFERROR(MONTH(@cell), 0) = 1)

     

    This replaces the missing month with the number zero. Because there are no month numbers of zero, this will not throw any false positives. In all reality, you can use any number that is not a month number, so 0 and then 13 through infinity. Any numbers 1 through twelve would need to have a decimal. 0 is just the easiest one to use because you can also use this in a YEAR, WEEKNUMBER, DAY, etc... Any function that requires a number really.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrea Mayer
    Andrea Mayer ✭✭✭✭✭

    Thank you, Paul! This worked perfectly. I appreciate the quick and helpful response.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!