Date & 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> Numbermenu. In the Sheets API, you set these formats using a spreadsheets.batchUpdate method call to send an UpdateCellsRequest or RepeatCellRequest .

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 depends 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 spreadsheets.get request.

About date & time values

Sheets, like most other spreadsheet applications, treats date and time values as decimal values. This lets you perform arithmetic on them in formulas, so you can increment days or weeks, add or subtract two dates and times, and perform other similar operations.

Sheets uses a form of epoch date that's commonly used in spreadsheets. The whole number portion of the value (left of the decimal) counts the days since December 30, 1899. The fractional portion (right of the decimal) counts the time as a fraction of one day. For example, January 1, 1900 at noon is 2.5 , 2 because it's 2 days after December 30, 1899, and 0.5 because noon is half a day. February 1, 1900 at 3 PM is 33.625 .

Sheets correctly treats the year 1900 as a common year, not a leap year.

Date & 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 & 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.

Date & time format examples

Given the date and time Tuesday, April 5, 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 :

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 don't 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 is treated as the text format and the others behave as if there's one less section. Thus, by including a final text format it's 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 before other parsing, so it's 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.

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 and a given section can use both:

Number format examples

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