Formatting Dates and Numbers

Modified on Wed, Nov 25, 2020 at 1:17 AM

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 (Passed 1340.89)
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 (Passed AbC123)
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.