Advanced Formulas

Not everybody is a fan of formulas. That doesn’t mean that you need to be handicapped at creating or understanding how formulas work in Stackby.

Stackby is easy to use even for the most non-technical user. So how can creating formulas on Stackby be any different for you as a user? “Apart from the basic Arithmetic Formulas (sum, average, subtraction, division, and multiplication, min, max, mode, average, etc.), Stackby supports 4 types of formulas. 

In this article, we will take a look at:

Date Formulas

CONCAT or Concatenate Formulas

Conditional Formulas (IF)

Nested Formulas

NESTED IF Formula 

Using Emojis in a Formula 

Date Formulas

These are formulas that use Date and time to perform calculations. Ideally, it is used when you need to:

• Add or subtract days or months to a current date
• Need to know the date difference (i.e. the number of days, months or years between two dates).
• Need to check the date and time for a different locale or timezone

It helps your set deadlines for the future (saves you the time of having to go and check the relevant date in a calendar) or go back to a previous date.

Now, say, you have an international team working remotely and you need to set their deadlines as per their time zone or locality, you can easily do that in Stackby.

One important thing to keep in mind is that you need to select "Date" in the format field while applying the Date related formulas (this field has a default setting for Integer, please change that to Date).

Here are a few examples to give you an idea.

You were given a project on a particular date and your deadline to finish and submit this project is 5 days from the date of allocation. So in the below example, there is a date of allocation. To calculate your deadline/date on which you need to submit the project, you need to use the DATE_ADD formula. Here is how to use the formula:

DATE_ADD({Date of Allocation}, 5, "Days")

The formula result will be the date that is 5 days ahead of the date of allocation. So, now, if you need a date addition that is 5 months or let us say 5 years down the line just change the formula to:

DATE_ADD({Date of Allocation}, 5, "Months")

DATE_ADD({Date of Allocation}, 5, "Years")

The result will be the date 5 years or months from the date of allocation. 

Similarly, you can use the below formula to subtract the days.

DATE_SUB(Date, 5, "days")

Now, let us say you need to calculate the number of days between two dates - you can do it easily with the below formula. So, here the Date in the bracket is the heading of the date column that you are using as a reference. The "Now" in the formula will be today's date.

DATE_DIFF(Date, NOW(), "days")

So the result will the number of days from the reference date till today or now.

For teams that work remotely from different locations across the world, we make it easy to view the deadlines in their time zone with a simple formula. So one of your teammates is remotely located in Australia and you want them to see their date and time of allocation in their time zone, you can change it with the below formula.

SET_TIMEZONE({Date of Allocation}, "Australia/Sydney")

With the above formula, you can see the Date of Allocation (reference column) date/time in the Australia/Sydney time zone. 

Having said that, it is not enough that they view the date of allocation only. They need to see their deadline as well - there are actually two ways you can do it. One is by creating a new column for the Australian deadline and adding the above "set timezone" formula. For this you need to select the general Deadline - Days column as a reference column.

SET_TIMEZONE({Deadline - Days}, "Australia/Sydney")

The result will be the "Deadline - Days" with the Australian timezone. Now, this means that you need to create another column and then add in the formula. We can do this with another formula, which will take in the Australian time zone as well as the deadline formula together. Here it is:

SET_TIMEZONE((DATE_ADD({Date of Allocation}, 5, "Days")), "Australia/Sydney")

CONCAT or Concatenate Formulas

Concatenate links together different fields (especially characters and text) in a chain or series like a string. A simple example would first name and last name - both of which you have put in separate columns in Stackby. You need to bring both together in a separate column. Because it is text, you need to use CONCAT formula to bring them together.

One important thing to keep in mind is that you need to select "String (Text)" in the format field while applying the CONCAT related formulas (this field has a default setting for Integer, please change that to String (Text)).

Always remember - it is easier to select the fields than typing it out. There is always scope for fewer mistakes while selecting fields (columns with heading comprising of two words and separated by a space are considered a field by our platform). So in the above case, say you have a column is named as First Name and another column named as Lastname, the formula will be:

{First Name}+ " "+ Lastname

Notice the difference in the way the fields have been formatted in the formula - the First Name column heading has a space in between and has been put in a curly bracket, whereas the Lastname is a single word and doesn’t need a curly bracket. 

Also, if you need to space or dash between two words (as with first and last names, just put the space between double inverted quotes between the two fields. Also do add a "+" at the end of the first field and another one at the beginning of the second field as in the above example.

Similar examples would be:

1) CONCAT({Quality of products},",",{Cost of products},",",{Customer Service})

2) Client+":" +{Order #}

The first of the above formulas will bring the fields together separated by a comma and the second one will bring the fields together separated by " : " 

Conditional Formulas (IF) 

As the name suggests, conditional formulas need to meet a certain condition to perform calculations. So they use - IF, And, Or in them. 

So, if you want to see the status of a project is done or not based on the boxes that are checked off, use this simple formula:

IF({IsCompleted ?} = "checked", "Done")

Nested Formulas

Nested formulas are an extension of conditional formulas - simply put, they have a formula within a conditional formula. Remember the Set Time Zone and Deadline - Day formula that we created above. That is an example of the nested formula - a formula within a formula.

SET_TIMEZONE((DATE_ADD({Date of Allocation}, 5, "Days")), "Australia/Sydney")

This is a simple nested formula. 

NESTED IF Formula 

You can also add IF conditions to the Nested Formulas. 

IF({IsCompleted ?} = "checked", "MET",IF(ISBEFORE({Due Date}, NOW()), "BEHIND","GOING ON"))

The above formula is used to determine the status of the project. The comparison is with the "task deadline" to "today's" date, and if the checkbox against it was checked off (that it was completed).

Using Emojis in a Formula

You can use also use emojis in nested formulas, especially, if you are showcasing say a deadline met. Inserting emojis is easy. All you need to do is to copy paste the emoji directly to the formula while creating it (you can choose your emoji from here: https://emojipedia.org/).

The same formula with emojis would be:

IF({IsCompleted ?} = "checked", "⌛MET",IF(ISBEFORE({Due Date}, NOW()), "⏳BEHIND","🔄GOING ON"))

OR

IF({Total Sales}>{Yearly Quota}, "🤩", "😞")