How do you create a formula to count how many entries there on a certain month?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

Each entry has a date with a dd-mm-yyyy format. How do I count the number of entries on that certain month? Is there even a way to do that?


Thanks!

Photo of Jean-Christer

Jean-Christer

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,432 Points 50k badge 2x thumb
There are lots of ways to do this, but one way is a simply Summary Report which groups by month.

Mark
Photo of Jean-Christer

Jean-Christer

  • 0 Points
I was looking to do it using Count formula but didn't know how set the conditions. Is this method plausible?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,432 Points 50k badge 2x thumb
Well, you can set up a table and load it  form Excel with text values in the format
2016-1
2016-2
2016-3
.
..
2026-12

Then on the details table you can make a formula field like

List("-", ToText(Year([My Date Field]),Month([My Date Field])

Then use that field as an anchor to a Relationship and then do a summary count of the # of entries.  That summary field could, I suppose, be looked up back down to each detail record so that that each detail record would know what the total # was for their monthly "cohorts".
Photo of Jean-Christer

Jean-Christer

  • 0 Points
This is really helpful however, I'm looking to do 2 more count formulas with 2 different fields of the same table, and add them altogether to have the sum in one field. I feel like this is getting too complicated sorry!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,432 Points 50k badge 2x thumb
Let's get the first total working first.  I forgot to say that in that table of dates, you need to set the key field to be that YYYY-MM field before you build the relationship.

Once you get that first one working, any other summary totals or counts can be done using that same relationship.
Photo of Jean-Christer

Jean-Christer

  • 0 Points
Apologies for not replying but big thank you for this by the way!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,432 Points 50k badge 2x thumb
Thx for letting me know!