Handling dates and times has been a key part of many apps built with Calcapp, ever since we introduced support way back in 2016. Our November release improved upon this support by making it possible to format dates and times using formulas with the new functions FORMATDATE and FORMATTIME.
However, one thing has remained the same: you are still limited to the date and time formats we support. Here they are, along with the names you need to use with FORMATDATE:
| Date format | US English example |
|---|---|
DateFormat.Month
|
Nov 1981 |
DateFormat.Numeric (default)
|
14/11/81 |
DateFormat.Abbreviated
|
Nov 14, 1981 |
DateFormat.Full
|
November 14, 1981 |
DateFormat.FullDayInWeek
|
Saturday, November 14, 1981 |
What if you want something a bit more custom, though? As things
stand, the only way to get the weekday to be part of the formatted
value is to use DateFormat.FullDayInWeek,
but then you also need to accept that the month is written out in
full, which may not be desirable.
You can customize how dates are formatted using formulas. This currently only works with output fields (that is, fields which display calculated values, as opposed to input fields that allow the user to enter data).
(If you’d like to format the values displayed in input fields using a formula, we’d love to make that happen. Write us and let us know, so we can prioritize this work!)
Using CHOOSE, WEEKDAY, MONTH, DAY and YEAR to format a date
We can use a formula to fully replicate what DateFormat.FullDayInWeek
does. Recall that this date format produces the following text:
Saturday, November 14, 1981
Now, we are looking to produce this text instead:
Saturday, Nov 14, 1981
Let’s assume that the date to format is contained in a date and time field named Date. Let’s also assume that we add a new text field, which will hold a formatted version of the Date value, using full weekdays and abbreviated month names.
Here’s the full formula, which we’ll dissect in a bit:
The formula above makes heavy use of the & operator, which joins text strings together. "ab" & "cd""ab" & "cd" returns “abcd”.
First off, let’s look at how deriving the weekday name from the Date value works. Here’s that part of the formula:
WEEKDAY returns a number corresponding to the day of the week. If a second parameter is not given, 1 is returned for Sunday, 2 is returned for Monday, and so on.
That’s perfect for the CHOOSE function, which returns the second parameter if the first parameter is set to 1, the third parameter if the second parameter is set to 2, etc. CHOOSE(2, "one", "two", "three")CHOOSE(2; "one"; "two"; "three") returns “two”. By plugging in the return value from WEEKDAY into CHOOSE, we get the behavior we’re looking for.
Next, we join together the weekday with “, “, followed by the abbreviated month name, using MONTH to extract a number corresponding with the month of the Date field, starting with 1. We then join together that text string with “ “ and DAY(Date)DAY(Date), which creates a space between the abbreviated month name and the day of the month.
Finally, we join together the text string with “, “ and YEAR(Date)YEAR(Date), which adds the year after a comma and a space. Now we’re done!
Using regular expressions to format a date (advanced)
The solution above successfully formats a date using full weekdays and abbreviated month names. To do so, however, it essentially reinvents what the FORMATDATE function does. What if we could take the output generated by that function and alter the month name, removing everything that follows its first three letters (so that “January” becomes “Jan”)?
We can, and there are multiple ways to do it. You could use the text processing functions Calcapp has inherited from spreadsheets, functions like FIND, MID, LEFT and RIGHT.
However, regular expressions are much more powerful than the old text processing functions, though there is a learning curve. When Calcapp gained support for regular expressions in 2018, we wrote a lengthy introduction, which we invite you to read.
Feel free to ignore this section if you like – using CHOOSE, WEEKDAY, MONTH, DAY and YEAR to format a date works perfectly well, and is much easier to understand.
REGEXREPLACE matches a text string against a regular expression, where the text string is given as the first parameter and the regular expression is given as the second parameter. The replacement text string is given as the third parameter.
This formula removes all numbers from a text string, returning “abc”:
The second parameter, \d is the regular
expression, which matches a single number (d stands for
digit). The third parameter ensures that all those numbers
are replaced with the empty text string, effectively meaning that
they are removed. The fourth parameter, TRUE, ensures that all
numbers are replaced, not just the first one.
Here’s a formula which takes the FORMATDATE output and shortens the month name:
Refer to our full primer on regular expressions to truly understand the regular expression above.
The first part, ^([^,]+, \w\w\w), uses
a capturing group to capture the weekday and the first three
letters of the month name. ^ matches the beginning
of the text string. The parentheses capture the entire contents as
the first capturing group. [^,]+ means “capture
everything up until the first comma.” , \w\w\w means “capture
the first three letters that follow a comma and a space.”
The final part, [^\d]*(.*)$ captures
the rest of the text string. [^\d]* means “capture
everything up until the first number” (representing the day). It is
not part of a capturing group (there are no parentheses) – as we
shall soon see, that essentially means that we throw it away.
Finally, (.*)$ means “capture
everything that remains, up until the end”. There are parentheses,
meaning that “everything that remains” is captured as the second
capturing group.
The third parameter to REGEXREPLACE is set to $1 $2. This means that
the resulting text string is composed of the contents of the first
capturing group, represented by $1 (containing the
weekday and the first three letters of the month name), followed by a
space, followed by the contents of the second capturing group,
represented by $2 (containing the day
and the year).
Depending on your perspective, this may seem like a much more difficult way to format the date than simply joining a few text strings together. Use whichever method you prefer.