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.

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article