Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Counting date entries by between months

Topic Labels: Base design
Solved
Jump to Solution
477 1
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi There,

I have a base that automatically date stamps when something moves through my content funnel. I would like to find a way to automatically count the number of date stamps for each month.

My leads are sorted by status: First Contact, Initial & Follow Up, CAM Meeting and Play Session. When I change a leads status it automatically puts a date into the below fields so I can see how many reached each point, each month.
Capture 1

I would like to have it so another field automatically counts how many date stamps there are in each month. For example, if I had 16 'First Contact leads and thus date stamps (as with the above image) in November, it automatically put it into the below table:

Capture 2

Any help would be appreciated, thanks!

1 Solution

Accepted Solutions

You’re going to need a series of helper fields or a script. The following solution doesn’t use scripts:

  • For each date stamp field, you need a companion Formula field to convert the date to your desired format, in this case “MMM-YY” to get “Dec-21”. Each formula would follow this pattern:
IF({Date Field}, DATETIME_FORMAT({Date Field}, "MMM-YY"))
  • Now create one Link to Another Record field for each date field all pointing to your Test table.
  • Copy the values of each Formula field into the corresponding Link field. This can be made automatic by using an automation, described further below.
  • In your Test table you will then be able to use Count fields to identify how many records hit a particular stage each month.

So to recap your first table will look like:
image

and your second table will look like:
image


Automation Set Up:
Trigger: When record is updated (select all 4 formula fields as the ones to watch)
Action Steps:
1. Update Record. Update the same record which triggered the automation by copying the values of the Formula fields into the Link fields. That would look like:
image
Notes: The Automation is designed to result in as few Automation runs as possible. Since you are only reporting by month, watching the Formula fields instead of the Date fields will make sure the Automation won’t refire if a date is changed from 12/1 to 12/2 since that’s still the same month. It also copies all Formula fields into all corresponding Link fields within the same action step so you don’t need to create one Automation per date field.

See Solution in Thread

1 Reply 1

You’re going to need a series of helper fields or a script. The following solution doesn’t use scripts:

  • For each date stamp field, you need a companion Formula field to convert the date to your desired format, in this case “MMM-YY” to get “Dec-21”. Each formula would follow this pattern:
IF({Date Field}, DATETIME_FORMAT({Date Field}, "MMM-YY"))
  • Now create one Link to Another Record field for each date field all pointing to your Test table.
  • Copy the values of each Formula field into the corresponding Link field. This can be made automatic by using an automation, described further below.
  • In your Test table you will then be able to use Count fields to identify how many records hit a particular stage each month.

So to recap your first table will look like:
image

and your second table will look like:
image


Automation Set Up:
Trigger: When record is updated (select all 4 formula fields as the ones to watch)
Action Steps:
1. Update Record. Update the same record which triggered the automation by copying the values of the Formula fields into the Link fields. That would look like:
image
Notes: The Automation is designed to result in as few Automation runs as possible. Since you are only reporting by month, watching the Formula fields instead of the Date fields will make sure the Automation won’t refire if a date is changed from 12/1 to 12/2 since that’s still the same month. It also copies all Formula fields into all corresponding Link fields within the same action step so you don’t need to create one Automation per date field.