DateTime
The date and time to format.
DateFormat
The date format to use when formatting the date. Use DateFormat.MonthDateFormat,Month for a text string only consisting of the month and year of the date ("Nov 1981"). Use DateFormat.NumericDateFormat,Numeric for a text string where the day, month and year appear as numbers ("11/14/1981"). Use DateFormat.AbbreviatedDateFormat,Abbreviated for a text string where the day and year appear as numbers and the month as an abbreviated version of its name ("Nov 14, 1981"). If the month should be written out in full, use DateFormat.FullDateFormat,Full ("November 14, 1981"). Use DateFormat.FullDayInWeekDateFormat,FullDayInWeek if the day of the week should be included ("Saturday, November 14, 1981"). All examples use US English date formatting. If omitted, DateFormat.NumericDateFormat,Numeric is assumed.
TimeFormat
The time format to use when formatting the time. Use TimeFormat.HourMinuteTimeFormat,HourMinute for a text string that does not include seconds and TimeFormat.HourMinuteSecondTimeFormat,HourMinuteSecond for a text string that does include seconds. If omitted, TimeFormat.HourMinuteTimeFormat,HourMinute is assumed.
Use24HourClock
Whether a 24-hour clock should be used, regardless of the time formatting conventions of the configured locale. If omitted, FALSE is assumed.
Locale
The locale to use when doing the conversion.
A locale represents a language and possibly a region. If omitted, the configured locale is used.
Use a value such as Locale.EnglishLocale,English to provide a locale (the full list is below). If your chosen locale is not part of the list, use the LOCALE function instead.
All locales may not be available. If a locale is not available, a fallback locale can be used. To provide such fallback locales, use the LOCALE function.
The LOCALE function accepts so-called Unicode Locale Identifiers. Every value in the list below corresponds to such an identifier.
| Formula | Unicode Locale Identifier | Description |
|---|---|---|
| Locale.ArabicLocale,Arabic | ar | Arabic |
| Locale.BulgarianLocale,Bulgarian | bg | Bulgarian |
| Locale.ChineseCnLocale,ChineseCn | zh-CN | Chinese (simplified), as used in mainland China |
| Locale.ChineseTwLocale,ChineseTw | zh-TW | Chinese (traditional), as used in Taiwan |
| Locale.CroatianLocale,Croatian | hr | Croatian |
| Locale.CzechLocale,Czech | cs | Czech |
| Locale.DanishLocale,Danish | da | Danish |
| Locale.DutchLocale,Dutch | nl | Dutch |
| Locale.DutchBeLocale,DutchBe | nl-BE | Dutch, as used in Belgium |
| Locale.EnglishLocale,English | en | English |
| Locale.EnglishAuLocale,EnglishAu | en-AU | English, as used in Australia |
| Locale.EnglishCaLocale,EnglishCa | en-CA | English, as used in Canada |
| Locale.EnglishInLocale,EnglishIn | en-IN | English, as used in India |
| Locale.EnglishNzLocale,EnglishNz | en-NZ | English, as used in New Zealand |
| Locale.EnglishUkLocale,EnglishUk | en-GB | English, as used in the United Kingdom |
| Locale.EnglishUsLocale,EnglishUs | en-US | English, as used in the United States of America |
| Locale.EnglishZaLocale,EnglishZa | en-ZA | English, as used in South Africa |
| Locale.FinnishLocale,Finnish | fi | Finnish |
| Locale.FrenchLocale,French | fr | French |
| Locale.FrenchBeLocale,FrenchBe | fr-BE | French, as used in Belgium |
| Locale.FrenchCaLocale,FrenchCa | fr-CA | French, as used in Canada |
| Locale.FrenchChLocale,FrenchCh | fr-CH | French, as used in Switzerland |
| Locale.GermanLocale,German | de | German |
| Locale.GermanAtLocale,GermanAt | de-AT | German, as used in Austria |
| Locale.GermanChLocale,GermanCh | de-CH | German, as used in Switzerland |
| Locale.GreekLocale,Greek | el | Greek |
| Locale.HebrewLocale,Hebrew | he | Hebrew |
| Locale.HindiLocale,Hindi | hi | Hindi |
| Locale.HungarianLocale,Hungarian | hu | Hungarian |
| Locale.IndonesianLocale,Indonesian | id | Indonesian |
| Locale.ItalianLocale,Italian | it | Italian |
| Locale.ItalianChLocale,ItalianCh | it-CH | Italian, as used in Switzerland |
| Locale.JapaneseLocale,Japanese | ja | Japanese |
| Locale.KoreanLocale,Korean | ko | Korean |
| Locale.NorwegianLocale,Norwegian | nb | Norwegian (bokmål) |
| Locale.PersianLocale,Persian | fa | Persian |
| Locale.PolishLocale,Polish | pl | Polish |
| Locale.PortugueseLocale,Portuguese | pt | Portuguese |
| Locale.PortugueseBrLocale,PortugueseBr | pt-BR | Portuguese, as used in Brazil |
| Locale.RomanianLocale,Romanian | ro | Romanian |
| Locale.RussianLocale,Russian | ru | Russian |
| Locale.SlovakLocale,Slovak | sk | Slovak |
| Locale.SpanishLocale,Spanish | es | Spanish |
| Locale.SpanishArLocale,SpanishAr | es-AR | Spanish, as used in Argentina |
| Locale.SpanishEsLocale,SpanishEs | es-ES | Spanish, as used in Spain |
| Locale.SpanishMxLocale,SpanishMx | es-MX | Spanish, as used in Mexico |
| Locale.SwedishLocale,Swedish | sv | Swedish |
| Locale.ThaiLocale,Thai | th | Thai |
| Locale.TurkishLocale,Turkish | tr | Turkish |
| Locale.UkrainianLocale,Ukrainian | uk | Ukrainian |
| Locale.VietnameseLocale,Vietnamese | vi | Vietnamese |
Returns
A date and time formatted as text.
Returns a date and a time as text. FORMAT.DATETIME ( DATETIME (1981, 11, 14, 15, 30, 0)) FORMAT.DATETIME ( DATETIME (1981; 11; 14; 15; 30; 0)), returns "11/14/1981, 3:30 PM" if the configured locale is US English. FORMAT.DATETIME takes the configured locale into account when converting dates.
Use FORMAT.TIME to format just a time. Use FORMAT.DATETIME to format both a date and a time.
This function is not commonly supported by spreadsheets.
Date format
The second parameter may be set to the desired date format. These are the allowed values:
| Date format | US English example |
|---|---|
| DateFormat.MonthDateFormat,Month | Nov 1981 |
| DateFormat.NumericDateFormat,Numeric (default) | 11/14/1981 |
| DateFormat.AbbreviatedDateFormat,Abbreviated | Nov 14, 1981 |
| DateFormat.FullDateFormat,Full | November 14, 1981 |
| DateFormat.FullDayInWeekDateFormat,FullDayInWeek | Saturday, November 14, 1981 |
Time format
The third parameter may be set to the desired time format. These are the allowed values:
| Time format | US English example |
|---|---|
| TimeFormat.HourMinuteTimeFormat,HourMinute (default) | 3:30 PM |
| TimeFormat.HourMinuteSecondTimeFormat,HourMinuteSecond | 3:30:00 PM |
Examples
Returns "11/14/1981, 3:30 PM" if the configured locale is US English. DateFormat.NumericDateFormat,Numeric is assumed if no second parameter is given and TimeFormat.HourMinuteTimeFormat,HourMinute is assumed if no third parameter is given.
Returns "11/14/1981, 3:30 PM". DateFormat.NumericDateFormat,Numeric is assumed if no second parameter is given and TimeFormat.HourMinuteTimeFormat,HourMinute is assumed if no third parameter is given.
Returns "11/14/1981, 3:30 PM" if the configured locale is US English.
Returns "14.11.1981, 15:30" if the configured locale is German.
Returns today's date followed by the current time.
Returns "Nov 1981, 3:30 PM" if the configured locale is US English.
Returns "Nov 14, 1981, 3:30 PM" if the configured locale is US English.
Returns "November 14, 1981 at 3:30:00 PM" if the configured locale is US English.