Tip: Custom formatting of dates

When formatting dates, you can choose from five pre-defined date formats. What if you want a custom date format? For output fields, you can use text fields and formulas to achieve this. This post explores two formula approaches.

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:

CHOOSE(WEEKDAY(Date), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday") & ", " & CHOOSE(MONTH(Date), "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec") & " " & DAY(Date) & ", " & YEAR(Date)CHOOSE(WEEKDAY(Date); "Sunday"; "Monday"; "Tuesday"; "Wednesday"; "Thursday"; "Friday"; "Saturday") & ", " & CHOOSE(MONTH(Date); "Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec") & " " & DAY(Date) & ", " & YEAR(Date)

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:

CHOOSE(WEEKDAY(Date), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")CHOOSE(WEEKDAY(Date); "Sunday"; "Monday"; "Tuesday"; "Wednesday"; "Thursday"; "Friday"; "Saturday")

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”:

REGEXREPLACE("a1b2c3", "\d", "", TRUE)REGEXREPLACE("a1b2c3"; "\d"; ""; TRUE)

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:

REGEXREPLACE(FORMATDATE(Date, DateFormat.FullDayInWeek), "^([^,]+, \w\w\w)[^\d](.)$", "$1 $2")REGEXREPLACE(FORMATDATE(Date; DateFormat,FullDayInWeek); "^([^,]+, \w\w\w)[^\d](.)$"; "$1 $2")

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.

« Tip: Add separators to reports Tip: Display all US legal holidays for a given year »