Number Format Codes

The following are the number formats that can be used in Zoho Sheet to create custom formats..

Number Code		Examples
Code Type	Description	Format Code	Input	Formatted Output
#	Hash sign (FormatHelp.HashSignMsg).	####	123	123
0	Zero (FormatHelp.ZeroMsg)	0000	123	0123
.	Decimal separator (FormatHelp.PeriodSymbol)	#.##	123.345	123.35
,	Thousand separator (FormatHelp.CommaSymbol)	#,###.00	1234	1,234.00
%	Percentage sign	#.00%	123	123.00%
E+ or E-	Scientific notations	0.00E0	1234	1.23E3
# ??/??	Fractional notations	# ??/??	1234.12	1234 3/25

Text Code		Examples
Code Type	Description	Format Code	Input	Formatted Output
@	Text placeholder (to format the number as text)..	@	1234	1234
$ - + / ( ) : ! ^ & ' ~ { } = < > space	These characters can be used without quotation marks or backslashes..	(@)	12345	(12345)
\character	To display the character as you specify.. (Note: FormatHelp.CharacterNoteMsg.)	\d	1234	d
"String"	To display a particular string as a format..	"String"	1234	String

Date Code
Code Type	Description
m	Months (1 – 12)	To display the month as a number without a leading zero..
mm	MonthsNumber Code (01 – 12)	To display the month as a number with a leading zero when appropriate..
mmm	MonthsNumber Code (Jan – Dec)	To display the month as an abbreviation..
mmmm	MonthsNumber Code (FormatHelp.january – FormatHelp.december)	To display the month as a full name..
d	Days (1 – 31)	To display the day as a number without a leading zero..
dd	Days (01 – 31)	To display the day as a number with a leading zero when appropriate..
ddd	Days (Sun – Sat)	To display the day as an abbreviation..
dddd	Days (Sunday – Saturday)	To display the day as a full name..
yy	Years (00 – 99)	To display the year as a two-digit number..
yyyy	Years (1900 – 9999)	To display the year as a four-digit number..

Time Code
Code Type	Description
H	Hours (0 – 23)	To display the hour as a number without a leading zero..
HH	Hours (00 – 23)	To display the hour as a number with a leading zero when appropriate.
m	Minutes (0 – 59)	To display the minute as a number without a leading zero..
mm	Minutes (00 – 59)	To display the minute as a number with a leading zero when appropriate.
s	Seconds (0 – 59)	To display the second as a number without a leading zero..
ss	Seconds (00 – 59)	To display the second as a number with a leading zero when appropriate.
HH:mm	Time (FormatHelp.displayas)	To display the time in 24-hour format..
h:mm a	Time (FormatHelp.displayas)	To display the time in AM/PM format..
HH:mm z	Time Zone (FormatHelp.displayas)	To display the time along with the time zone..
(Note: FormatHelp.MinInsteadOfMonths.)

Miscellaneous Symbols
Code Type	Description	Format Code	Input	Formatted Output
[COLOR] [Condition value]	Supported colors: BLACK BLUE CYAN GREEN MAGENTA RED WHITE YELLOW The condition can be <, >, =, or <>, and the value can be any number.. (Note:FormatHelp.CondMsgNote.)	[GREEN]#.00;[RED]$00.0000;[CYAN]#.#;[BLUE]@ [MAGENTA]@ [<=18][GREEN]"CHILD";[>18][RED]"FormatHelp.ADULT" [>=1000][MAGENTA]0.000;[<1000][CYAN]#.00000	1234 -1234 0 Sheet 1234 Sheet 13 26 1234 123	1234.00 -1234.0000 0 Sheet 1234 Sheet CHILD FormatHelp.ADULT 1234.000 123.00000
(Note: FormatHelp.ColorNote.)

Join the #ZohoSheet family

Follow us to stay up-to-date with all the latest updates, events, and more.

© 2024 Zoho Corp. All rights reserved.