Date and Number Formats

Date-time and number formats let you control how your data appears in a sheet. Google Sheets provides some common formats to choose from, but you can also define your own formats.

In the Sheets UI, you apply number and date formats to cells using the Format > Number > More Formats... menu. In the Sheets API, you set these formats using a spreadsheets.batchUpdate method call to send a UpdateCells or RepeatCell request.

This page describes how to define new date and number format patterns, which you can include in your API request. The sample Set a custom datetime or decimal format for a range shows how to set a format pattern using the API. Note that the actual rendering of the format is dependant on the spreadsheet's locale; this guide assumes the locale is en_US. You can determine the locale of a spreadsheet by reading the SpreadsheetProperties with a spreadsheet.get request.

Date and time format patterns

A date-time format pattern is a string of token substrings that, when parsed, are replaced with the corresponding date-time elements (such as the month or hour).

Date and time format tokens

The following table defines the token substrings you can use in a date-time format pattern. A + character indicates that the previous character can appear one or more times and still match the pattern. Characters not listed in the table below are treated as literals, and are output without changes.

Token Description
h Hour of the day. Switches between 12 and 24 hour format depending on whether an am/pm indicator is present in the string.
hh+ Same as previous, but with a leading 0 for 1-9.
m If the previous non-literal token was hours or the subsequent one is seconds, then it represents minutes in the hour (no leading 0). Otherwise, it represents the month of the year as a number (no leading 0).
mm As above, but with a leading 0 for both cases.
mmm Three letter month abbreviation (e.g., "Feb").
mmmm Full month name. mmmmmm+ also matches this.
mmmmm First letter of the month (e.g., "J" for June).
s Seconds in the minute without a leading 0.
ss Seconds in the minute with a leading 0.
[h+] Number of elapsed hours in a time duration. Number of letters indicates minimum number of digits (adds leading 0s).
[m+] Number of elapsed minutes in a time duration. Number of letters indicates minimum number of digits (adds leading 0s).
[s+] Number of elapsed seconds in a time duration. Number of letters indicates minimum number of digits (adds leading 0s).
d Day of the month, no leading 0 for numbers less than 10.
dd Day of the month, with a leading 0 for numbers less than 10.
ddd Day of the week, three letter abbreviation (e.g., "Mon").
dddd+ Day of the week, full name.
y 2-digit year.
yy
yyy 4-digit year.
yyyy+
a/p Displays "a" for AM, and "p" for PM. Also changes hours to 12-hour format. If the token letter is capitalized, the output is as well.
am/pm As above, but displays "AM" or "PM" instead and is always capitalized.
0 Tenths of seconds. You can increase the precision to 2 digits with 00 or 3 digits (milliseconds) with 000.
\ Treats the next character as a literal value and not any special meaning it might have.
"text" Displays whatever text is inside the quotation marks as a literal.

Date and time format examples

Given the date and time Tuesday, April 5th, 2016, 4:08:53.528 PM, the following table shows some example patterns and their corresponding date-time rendering. The second section of the table shows examples of elapsed time formats for an elapsed time of 3 hours, 13 minutes, 41.255 seconds:

Date-time Patterns Tuesday, April 5th, 2016, 4:08:53.528 PM
h:mm:ss.00 a/p 4:08:53.53 p
hh:mm A/P".M." 04:08 P.M.
yyyy-mm-dd 2016-04-05
mmmm d \[dddd\] April 5 [Tuesday]
h PM, ddd mmm dd 4 PM, Tue Apr 05
dddd, m/d/yy at h:mm Tuesday, 4/5/16 at 16:08
Elapsed Time Patterns 3 hours, 13 minutes, 41.255 seconds
[hh]:[mm]:[ss].000 03:13:41.255
[mmmm]:[ss].000 0193:41.255

Number format patterns

A number format pattern is a string of token substrings that, when parsed, are replaced with the corresponding number representations. A number format pattern can consist of up to four sections, separated by semicolons, which define the separate formats used for positive numbers, negative numbers, zero, and text (in that order):

[POSITIVE FORMAT];[NEGATIVE FORMAT];[ZERO FORMAT];[TEXT FORMAT]

You do not need to include all four sections in a format. If you only include one section, that format is used for all values. Using two sections causes the first format to be applied to zero and positive numbers and the second format to negative numbers. Using three sections defines separate formats for positive, negative and zero numbers. For example:

[NUMBER FORMAT]
[POSITIVE/ZERO FORMAT];[NEGATIVE FORMAT]
[POSITIVE FORMAT];[NEGATIVE FORMAT];[ZERO FORMAT]

However, if there are two or more sections and the final section is a text format, that section will be treated as the text format and the others will behave as if there is one less section. Thus, by including a final text format it is possible to define formats such as:

[POSITIVE/ZERO FORMAT];[NEGATIVE FORMAT];[TEXT FORMAT]
[NUMBER FORMAT];[TEXT FORMAT]

The parsing of a format into sections occurs prior to other parsing, so it is possible to have a date or time format included as one of the sections (though this is of limited utility).

Number format tokens

The following table defines the token substrings you can use in a format section to define how to represent the values for that section.

Token Description
0 Represents a digit in the number. If the digit is an insignificant 0, it is rendered as 0. For example, the number format 00.0 renders the number 3 as "03.0".
# Represents a digit in the number. If the digit is an insignificant 0, it is not rendered. For example, the number format ##0 renders the number 12 as "12".
? Represents a digit in the number. If the digit is an insignificant 0, it is rendered as a space. This is often used to align the decimal point in a column when using fixed-width fonts. For example, the number format ???.??? renders the number 12.4 as: " 12.4 ".
. The first period represents the decimal point in the number. Subsequent periods are rendered as literals. If you include a decimal point in the format, it will always be rendered, even for whole numbers. For example, #0.# will render the number 3 as "3.".
% Appears as a literal but also causes existing numbers to be multiplied by 100 before being rendered, in order to make percentages more readable.
, If it appears between two digit characters (0, # or ?), then it renders the entire number with grouping separators (grouping by the thousands). If it follows the digit characters, it scales the digits by one thousand per comma (e.g., the format #0.0,, renders the number 12,200,000 as 12.2).
E- Renders the number in scientific format, with the formatting to the left of the E used for the non-exponent portion and the formatting to the right of the E used for the exponent portion. E+ will show a + sign for positive exponents. E- will only show a sign for negative exponents. If lowercase is used, the output e is lowercased as well.
E+
e-
e+
/ If it appears between two digit characters (0, # or ?), then it treats those digit groups as a fractional format. For example, the number format 0 #/# renders the number 23.25 as 23 1/4. The denominator can also be a literal integer, in which case it will enforce that integer as the denominator. The number format 0 #/8 displays the number 23.25 as 23 2/8. The fraction part is not rendered at all if the numerator would become 0. The number 23.1 with the number format 0 #/3 renders as just 23 (because the 0.1 rounded to 0/3). / is not compatible with scientific format or a format with a decimal point in it.
* This is included for compatibility with Excel number formats. It is currently ignored.
_ Skips the next character and renders a space. This is used to line up number formats where the negative value is surrounded by parenthesis.
\ Treats the next character as a literal value and not any special meaning it might have.
"text" Displays whatever text is inside the quotation marks as a literal.
@ Inserts the raw text for the cell, if the cell has text input. Not compatible with any of the other special characters and won’t display for numeric values (which are displayed as general format).
$ - + ( ) : space Any of these characters will be treated as a literal and displayed unchanged.

Meta instructions

In addition, each of the format sections can have optional meta instructions, enclosed in [] characters, that precede the format and provide additional instructions. There are two meta instruction types; a given section can use both:

Instruction Description
[condition] Replaces the default positive, negative, or zero comparison of the section with another conditional expression. For example, [<100]”Low”;[>1000]”High”;000 will render the word “Low” for values below 100, “High” for values above 1000 and a three digit number (with leading 0s) for anything in between. Conditions can only be applied to the first two sub-formats and if a number matches more than one, it will use the first one it matches. If there is a third format, it will be used for "everything else", otherwise if a number doesn’t match either format, it will be rendered as all "#"s filling up the cell width. The fourth format is always used for text, if it exists.
[Color] or [Color#] Causes any value that is rendered by this sub-format to appear with the given text color. Valid values for Color are Black, Blue, Cyan, Green, Magenta, Red, White or Yellow.
Valid values for the "#" in Color# are 0 - 56 (this color palette shows a list of the colors that correspond to each number). Number format colors will override any user-entered colors on the cell, but will not override colors set by conditional formatting.

Number format examples

The following table shows some example patterns and their corresponding formatted number rendering:

Number Pattern Formatted Value
12345.125 ####.# 12345.1
12.34 000.0000 012.3400
12 #.0# 12.0
5.125 # ???/??? 5 1/8
12000 #,### 12,000
1230000 0.0,,"M" 1.2M
1234500000 0.00e+00 1.23e+09
123114.15115
MyText
###0.000;"TEXT: "_(@_) 123114.151
TEXT: MyText
1234
-1234
0
MyText
[Blue]#,##0;[Red]#,##0;[Green]0.0;[Magenta]_(@_) 1,234
1,234
0.0
MyText
1005
32
527
[>1000]"HIGH";[Color43][<=200]"LOW";0000 HIGH
LOW
0527

Send feedback about...