FORMAT- FineBI Help Document

Last update：November 20, 2025

Overview

In scenarios where frequent format conversion is required during data analysis, FineBI provides the FORMAT function specially for field formatting.

Syntax	FORMAT(Object,Format)	Returns an object in a specified format.
Parameter 1	Object	Object to be formatted, which can be a string, number, or object (usually date and time) Note: For real-time data, the value of Object can only be of the date type.
Parameter 2	Format	Object format

Syntax

FORMAT(Object,Format)

Returns an object in a specified format.

Parameter 1

Object

Object to be formatted, which can be a string, number, or object (usually date and time)

Note:

For real-time data, the value of Object can only be of the date type.

Parameter 2

Format

Object format

Notes

The function supports two parameters. For extracted data, the first parameter can be of any type, and the second is of the text type.
For real-time data, the first parameter is of the date type and the second is of the text type.
To format a date through the FORMAT function, the date must be in the yy/yyyy-M/MM-d/dd format.
The FORMAT function is unavailable for the directly connected Kyligence database (enterprise edition).

Example

The following section takes 2021-06-15 as an example to introduce common FORMAT parameters, including date-type and numeric parameters.

Date Format Parameter

Format Parameter	Output
D	166
DD	166
M	6
MM	06
MMM	June
MMMM	June
YY	21
YYYY	2021
YYYYMM	202106
MM/dd/yyyy	06/15/2021
M-d-yy	6-15-21
EEEEE, MMMMM dd, yyyy	Tuesday, June 15, 2021
h:mm:ss a	12:00:00 AM

For example, if you want to remove the hour data from the date string, you can click Formula Column, name the column, enter the formula TODATE(FORMAT(Column to be formatted,"yyyy-MM-dd")), and click OK to create the formula column.

The following figure shows the result.

Numeric Format Parameter

This section takes the numeric value 1,234.56 as an example.

Parameter Format	Formula	Output
#,##0.00	FORMAT(1234.5,"#,##0.00")	1,234.50
#,##0	FORMAT(1234.5,"#,##0")	1,234
￥#,##0.00	FORMAT(1234.5, "￥#,##0.00")	￥1,234.5
0%	FORMAT(1.5, "0%")	150%
0.000%	FORMAT(1.5, "0.000%")	150.000%
##0.0E0	FORMAT(6789, "##0.0E0")	6.789E3
0.00E00	FORMAT(6789, "0.00E00")	6.79E03

0: The zero placeholder is used to display a number with leading zeros in positions without digits.
#: The number placeholder is used to display a digit. If the position is empty, nothing will be displayed.
%: The composite format specifier is used to multiply the number by 100 and append % to the right.

Combination with Other Functions

Formula	Result	Remarks
FORMAT(date(2007,1,1), "EEEEE, MMMMM dd, yyyy")	Monday, January 01, 2007
FORMAT(date(2007,1,13), "MM/dd/yyyy")	01/13/2007
FORMAT(date(2007,1,13), "M-d-yy")	1-13-07
FORMAT(time(16,23,56), "h:mm:ss a")	4:23:56 PM

Previous：INDEXOF - Returning the Character at a Specified Position in Text

Next：FIND - Returning the Position of a Specified Character in Text

Helpful
Not helpful
Only read

中文（简体）中文（繁體）日本語

English

FORMAT