If you want to group your records on a date range—for example, to see every transaction record that was made by week, or every deal by the quarter in which it occurred—you can create a formula field with DATETIME_FORMAT() and group on that formula field.
By using different format specifiers in your DATETIME_FORMAT() formula, you can group by day of the week, week of the year, month, quarter, and more.
Alternatively, instead of using the DATETIME_FORMAT(), you can also use the preset YEAR(), MONTH(), WEEKDAY(), or WEEKNUM() functions. You can read more about these functions in the formula field reference article.
If you choose to group on a date field that includes a time field, you will group by both the date and
time. If you aren't looking to include the time in your grouping, we recommend creating a separate formula field
to parse just the date and grouping by that field instead.
Example 1: Grouping records by week
Let's say that you have a table of sales conversations, and you want to be able to group the outreach records by week in order to get a sense of how much outreach you've done each week for the past few weeks.
If you try to group on the Date field, only conversations that occurred on the same day will get grouped together. To group on the week instead, you'll need to make a formula field using DATETIME_FORMAT(). DATETIME_FORMAT() requires you to specify a date field (which we already have) and a format specifier, which determines the output of the formula. (You can find a full list of our supported format specifiers on this page.)
Since you want to group on the week of the year, and the format specifier for the week of the year is 'w', you write this formula:
Now, you can group on the newly created Week No. field to see all the outreach events grouped by the week in which they happened:
Example 2: Grouping records by year and quarter
Let's suppose that you have a table of closed deals spanning multiple years, and you want to have some way to see the total size of the deals made by quarter AND year.
To create a DATETIME_FORMAT() formula field that groups on quarter, use this formula:
However, if you just group on this new field, records from different years (but the same quarters) will get grouped together. The solution is to create a second formula field for the year:
If you create two layers of grouping (first grouping on Year, then grouping on Quarter), you can take advantage of the grouped record summary bar to get valuable data insights.