Wanted to share a quick update that we’ve added a few new formulas to the Coda formula language that you might find useful in your docs.
Part 1: The New ToTime() Formula
Convert a DateTime value into a Time value
=ToTime(Now()) returns 9:35:32 AM. In the past you had to write
=Time(Now().Hour(), Now().Minute(), Now.Second()) to get the same result.
=thisRow.created().ToTime() returns 11:07:48 PM.
Convert a compatible string into a Time value
=ToTime("9:00pm") returns 9:00 PM
=ToTime("11:10:00am") returns 11:10:00 AM
Part 2: New Contains Formulas
Coda already has a Contains formula which works as follows:
=List("Apple", "Orange").Contains("Apple") returns true
=List("Apple", "Orange").Contains("Banana") returns false
Most people don’t know that the Contains() formula is actually “Contains Any Of”. You can enter more than one parameters or a list and the formula returns true if any of the values you specified is in the list you are searching.
For example, let’s say you have the following tasks table:
You can write the following filter formula:
=Assignee.Contains([Angad Singh],[Matthew Tebbs])
This will return true for Task 1, 3 and 4. It will only return false on Task 2 since that is the only task that does not contain either Angad or Matthew.
The New ContainsAll() Formula
In the table above, let’s say you only want to see tasks that are assigned to both Angad and Matthew instead of either one of them. Currently you need to write the following formula:
=Assignee.Contains([Angad Singh]) AND Assignee.Contains([Matthew Tebbs])
We have now added a new formula called ContainsAll(), which returns true only if the value you are comparing to has all of the values from the search list. So you can now write the above formula as:
=Assignee.ContainsAll([Angad Singh],[Matthew Tebbs])
This formula will return true on Task 3 and 4, since they contain both Angad and Matthew. Unlike Contains(), it will not return true on Task 1, since it contains Angad but not Matthew.
The New ContainsOnly() Formula
In the table above, let’s say you only want to see tasks that are assigned to both Angad & Matthew but not anyone else. You could write the following formula:
=Assignee=List([Angad Singh],[Matthew Tebbs])
However, the formula above would not return true if the Assignee list was in the opposite order or had duplicates. So the correct formula would have to be:
=Assignee.Unique().Sort() = List([Angad Singh],[Matthew Tebbs]).Unique().Sort()
Instead, you can now just write the following formula:
=Assignee.ContainsOnly([Angad Singh],[Matthew Tebbs])
This formula will return true only on Task 3, since it is the only one that contains both Angad and Matthew and no other values. Unlike ContainsAll(), it will not return true on Task 4, since that contains Angad and Matthew but also Moriah.
Here’s a doc that shows all three Contains Formulas in action:
Hope these formulas are useful in your doc!