Pull specific parts of a name

If you have a full name in a column but need to mail merge on a first name, this formula will separate (splice) it into parts.

= $'[Column Name]'.substring(0, $'Name'.indexOf(' '));

Only the last name

= $'[Column name]'.split(" ").slice(-1)

Count the number of objects associated with a box

We use this internally to keep track of how many reports relate to a single box (i.e. Feature Requests). 

The prerequisite for this formula is an All Linked Boxes column. 

= $'All Linked Boxes'.length

This formula can be used to count the number of objects in a column, too (the number of Contacts, for instance).

= $'Assigned To'.length

Data-correct inputs for common errors

Streak can only create summaries when data are all of the same type. If a Deal Size or Money related column has characters in it that are not numbers from 0-9 – e.g. commas or currency symbols – it will be unable to summarize the data for reporting purposes.

You can correct this for integers by using a formula column that is data-correct:

= if $'[Column]' % 1 === 0) { return $'[Column]' } else { return $'[Column]'.replace(/[^[0-9]]*/g, "") }

Date formula

Our formatting options can be expanded by using a formula to pull more information into the spreadsheet view.

Get the month from a date

= var date = new Date($'[Date column]');
return date.getMonth()+1;

Get the year from a date

= var date = new Date($'[Date column]'); 
return date.getFullYear();

Get the calendar week from a date

= var d = new Date($'Date of Last Stage Change'); 

function getWeek(d) {
var target = new Date(d.valueOf());

var dayNr = (d.getDay() + 6) % 7; target.setDate(target.getDate() - dayNr + 3);

var jan4 = new Date(target.getFullYear(), 0, 4); var dayDiff = (target - jan4) / 86400000; var weekNr = 1 + Math.ceil(dayDiff / 7); return weekNr;

return getWeek(d);

Did this answer your question?