Counting dates as entries

Mtmoroni
Mtmoroni ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

I am compiling a metrics page, and need to count a date field, teh automated "created" which stores date and time the row was created.  I want to count a total of "01/XX/19", XX meaning i dont care about the days, i just want all of January 2019 entries.

I am refrencing another sheet (hence the metric sheet), so i need to do that.  The goal is to utilize for each month/2019, as well as each month/2018, and continue in years to come.

Thanks!

Tags:

Comments

  • Andrée Starå
    Andrée Starå Community Champion

    Hi,

    Try this.

    Change the last number (4) to which month you want to count. The IFERROR part will remove an error if there is a cell that isn't a date in the range (not needed for the created column but if you want to reference another date column)

    =COUNTIFS({Reference to other sheet}; IFERROR(MONTH(@cell); 0) = 4)    

    The same version but with the below changes for your and others convenience.  

    =COUNTIFS({Reference to other sheet}, IFERROR(MONTH(@cell), 0) = 4)

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome Community Champion

    To further Andree's solution, to include the YEAR requirement, we can just use an AND function with @cell references in the criteria section...

     

    =COUNTIFS({Reference to other sheet}, AND(IFERROR(MONTH(@cell), 0) = 4, IFERROR(YEAR(@cell), 0) = 2019))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!