Coerce blank to lowest date value with ("").toDate()

Sometimes date values in one column, A1, will need to be compared to a date value in another column, say it’s named D1.

If D1 is user entered, the user might not enter a date–don’t know what it is yet, haven’t decided, etc. This should be typical for a product like Coda. It doesn’t try to enforce all the rules that a relational database would require. In other words, Coda needs to handle null, or missing or in Coda-speak, Blank, gracefully.

Alas, Coda does not…

if D1 = “”, then date(2022,12,1) > D1 will always return false because of the type mismatch. You can replace the date function literal with A1–assuming it does always actually contain a valid date value.

So, we have to coerce D1 to be a low (early in history) date.
We can do that with:
if(thisrow.D1.isBlank(), Date(1900, 1, 1), thisrow.D1)

Now, lets say that the actual name of the column is quite long, such is very common in Coda so that columns are meaningful to users. So, we then might be compelled to us a withName block to avoid repeating the long name.

Then we get this:

                  thisRow.[Last Completed (Lustrous)],
                  Date(2022,12,1) > if(d.IsBlank(), date(1900, 1, 1), d)

This just returns true or false for the simple comparison to either d or a low date value if d is blank. If, more realistically, we want set a result value based on the comparison, then we have to embed all of that within an if statement.

Then we could get a bigger mess if both sides of the date comparison need to be tested for blankness:

    [Lustrous All Tasks]     # a view in my document
        [Routine Item] = thisRow
      .[Planned Start],     # a column name in the source table
                  thisRow.[Last Completed (Lustrous)],     # assume this is the user input date
                  Max(n) > if(d.IsBlank(), date(1900, 1, 1), d)

Being polite, this is infelicitous. This is a bear. In my example, one date comes from a collection in another table. I test it for Blank-ness and I take it’s Max, which will work for a scalar or an array of dates. Then, I compare this to a user entered date, that might be there or not.

D1.toDate() should work for valid string literal representations of dates, as it does today


when D1 is ""
D1.toDate() should return the lowest valued date permitted by the Coda interpreter. Because the internal date is an integer, often called a Julian date, then we return the date for either 1 or 0. Preferably 0 so that D1.toDate() >= ("").toDate() is always true when D1 represents any date in the permitted range of dates.

Now, we don’t need to test the date twice: once to resolve a blank date and once to do the intended comparison.

I grant this is somewhat of a special case, but because Coda doesn’t have a NULL or MISSING value (see Julia or Python Numpy) for every numerical type in Coda (probably just Int64 or Float64–though internally I bet Coda does everything in floating point) we can make the conversion function provide a way to convert the NULL value to the appropriate zero value.

I acknowledge that NULL often does not mean zero in numerical problems, but Coda is not exactly the language one would choose for numerical methods or machine learning.

(P.S. It is really great that I can create these formulas in a text editor and paste them in a Coda usually does the correct replacement for the reference.)

Hi Lewis:

Or you could default a date…


Rambling Pete

1 Like

I did over simplify a bit (and it wasn’t that simple…).

My “user entered” date is actually retrieved by another formula–but that does come from another table that does contain a user entered date.

I am concerned that the default date would appear and be weird. It wouldn’t make sense for users to see a planned date or completed date like 1/1/1900 that they didn’t enter.

If this null date could automagically format as blank then this would be an OK solution. Can conditional formatting change the display to look blank? I supposed I could test on date for my default value and set the text color to white.

HI Lewis,

I suggest to just move your default date setting then to the table where it initially gets entered by the user…

But you seem to have changed your mind, and no longer want a default date, you just want blank. Is that correct?


Thanks. I fixed it. It was a data type coercion problem. Must use concatenate() and not + when combining a string and date value. + coerces the date to “number” and then the text version of the number. You get “new 488808.34589”. Not what I wanted. Concatenate provides the text representation of the date value and then catenates it with a string. You get “new 2022/09/10 5:32 PM”. Better. Subtle. Not in docs anywhere. But, found answer in the forum.

Thanks for keeping after it.