Custom columns: Formulas and custom dates/times


#1

Formula

The Formula tab provides the easiest way to define your computed columns by entering your formula in the expression editor. When you click in the editor, a list of existing columns is displayed.

You can include operators (*, /, +, -) and parentheses in your expression.

Press Enter to complete the current statement (you will notice that the font is updated and an empty space is added). Note that it is not possible to insert an operator or a column name before a previous statement; you must delete the previous statements first.

You can then use the Format list and buttons to adjust the computed column’s format.

Validation

After each statement (separated by spaces), a validation module will run to help you ensure that your formula doesn’t contain any errors. As soon as an error is detected, it will be underlined in red and you’ll be prompted to correct it before continuing.

The validation module includes keeping track of parentheses and incompatible operators (+ followed by /), and will ensure that column names are valid. It will also ensure that your formats are compliant with the format of data you’re working with. For example, you cannot use the Number format on a column representing date values.

custom-columns

Custom date & time formats

d: the day of the month
dd: the day of the month
ddd: the abbreviated name of the day of the week
dddd: the full name of the day of the week
h: the hour, using a 12-hour clock from 1 to 12
hh: the hour, using a 12-hour clock from 01 to 12
H: the hour, using a 24-hour clock from 0 to 23
HH: the hour, using a 24-hour clock from 00 to 23
m: the minute, from 0 through 59
mm: the minutes,from 00 though 59
M: the month, from 1 through 12
MM: the month, from 01 through 12
MMM: the abbreviated name of the month
MMMM: the full name of the month
s: the second, from 0 through 59
ss: the second, from 00 through 59
t: the first character of the AM/PM designator
tt: the AM/PM designator
y: the year, from 0 to 99
yy: the year, from 00 to 99
yyy: the year, with a minimum of three digits
yyyy: the year as a four-digit number;
yyyyy: the year as a four-digit number.

Examples

dd/MM/yyyy

yy.MM.dd

dddd dd MMMM yyyy

HH:mm ss

Resources

For information on computing in JavaScript, see the Custom columns: JavaScript computing topic.

For information on custom display functions in JavaScript, see the Custom columns: JavaScript custom display functions topic.