Formatting Dates and Numbers
You can specify the date and number formatting and apply string functions after formatting. Refer to the following specifiers and example outputs.
Date and Number Formatting
A user-specified format string for dates and numbers. Create your custom format string using the specifiers below. Be sure to use date specifiers with date values and numeric specifiers with numeric values.
Date Formatting
Specifier | Type | Example Output |
dd | Day | 10 |
ddd | Day abbreviation | Tue |
dddd | Full day name | Tuesday |
hh | 2 digit hour | 10 |
HH | 2 digit hour, 24hr format | 22 |
mm | Minute 00-59 | 38 |
MM | Month 01-12 | 12 |
MMM | Month appreviation | Dec |
MMMM | Full month name | December |
tt | AM or PM | PM |
yy | Year, 2 digits | 02 |
yyy | Year | 2002 |
: | Separator | hh:mm:ss 10:43:20 |
/ | Separator | dd/MM/yyyy 10/12/2002 |
Example: hh:mm tt yields 9:28 am or 9:28 pm
Number Formatting
Name | Type | Example | Output |
0 | Zero placeholder | 00.0000 | 1340.8900 |
# | Digit placeholder | (#).## | (1340).89 |
. | Decimal | 0.0 | 1340.9 |
, | Thousand separator | 0,0 | 1,300 |
% | Percent | 0% | 134089% |
e | Exponent placeholder | 00e+0 | 13e+2 |
String Functions
Optional string functions applied after formatting. If a string function requires parameters (i.e. specifies values in parenthesis) they you must provide the exact number of values in the (inputs) field. String values (str) should be enclosed in quotes ("). Integer values are specified by (int).
String Formatting
Function | Type | Example | Output |
Left(int) | Returns the (int) leftmost characters | Left(4) | AbC1 |
Right(int) | Returns the (int) rightmost characters | Right(2) | 23 |
Replace(str,str) | Replaces all instances of the first string with the second string | Replace("C","-----") | Ab-----12 |
Substring(int,int) | Returns the substring that starts at the first integer (0 is start of string) with a length of the second integer | Substring(1,3) | bC1 |
ToUpper() | Converts string to Upper Case characters | ToUpper() | ABC123 |
ToLower() | Converts string to Lower Case characters | ToLower() | abc123 |
MonthName() | Returns the full month name if passed a integer value 1-12 | MonthName() | na |
Note: For detailed string functions, see Column Formula Quick Reference and go to String Functions.