Date & number formats  |  Google Sheets  |  Google for Developers (2024)

• Home
• Google Workspace
• Google Sheets
• Guides

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 alsodefine your own formats.

In the Sheets UI, you apply number and date formats to cells using theFormat > Number menu. In the Sheets API, you set these formats using aspreadsheets.batchUpdatemethod call to send anUpdateCellsRequestorRepeatCellRequest.

This page describes how to define new date and number format patterns, which youcan include in your API request. The sampleSet a custom datetime or decimal format for a rangeshows how to set a format pattern using the API. Note that the actual renderingof the format depends on the spreadsheet's locale. This guide assumes thelocale is en_US. You can determine the locale of a spreadsheet by readingtheSpreadsheetPropertieswith a spreadsheets.getrequest.

About date & time values

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

Sheets uses a form ofepoch datethat's commonly used in spreadsheets. The whole number portion of the value(left of the decimal) counts the days since December 30, 1899. The fractionalportion (right of the decimal) counts the time as a fraction of one day. Forexample, January 1, 1900 at noon is 2.5, 2 because it's 2 days afterDecember 30, 1899, and 0.5 because noon is half a day. February 1, 1900 at 3 PMis 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 orhour).

Date & time format tokens

The following table defines the token substrings you can use in a date-timeformat pattern. A + character indicates that the previous character can appearone or more times and still match the pattern. Characters not listed in thetable 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, thefollowing table shows some example patterns and their corresponding date-timerendering. The second section of the table shows examples of elapsed timeformats 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, arereplaced with the corresponding number representations. A number format patterncan consist of up to four sections, separated by semicolons, which define theseparate formats used for positive numbers, negative numbers, zero, and text (inthat 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 onesection, that format is used for all values. Using two sections causes the firstformat to be applied to zero and positive numbers and the second format tonegative 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 textformat, that section is treated as the text format and the others behave as ifthere's one less section. Thus, by including a final text format it's possibleto 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'spossible to have a date or time format included as one of the sections (thoughthis is of limited utility).

Number format tokens

The following table defines the token substrings you can use in a format sectionto 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 provideadditional instructions. There are two meta instruction types and a givensection can use both:

Number format examples

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