Date

The Date data type is for inputting date values according to the formatting that you set.

Setting a column, cell, or range of cells as a Date data type, you can ensure that users input dates in a consistent format. Also, there are multiple formulas you can apply to dates, including Duration which measures the number of days between two dates.

Use the Date data type whenever you need to set the date. For example, you can track due dates for when tasks are due, the birthdays of your employees, or use dates and formulas to see how many days have elapsed and how many days are left before your team's next milestone.

The Date data type only tracks dates, not times. Use our Date & time data type to track time and date.

Date.png

Configuration Options

Strict

Like other data types, the Date data type can have Strict enabled. Selecting the Strict checkbox for Date cells will restrict users to input only dates. Also, columns that have Strict enabled cannot have cells of different data types.

Format and Locale

The formatting options available to you are dependent on the Locale you select. By default, each worksheet's location matches that of the creator, so the default locale suggested in the date picker will be the creator's locale. You can change this by selecting from one of 27 languages/locales. 

Each locale has its own format types to correspond to that country and language. The available formatting options based on locale are as follows, with the default format in bold:

Locale (location) Format
English (United States)
  1. 03/14/2012
  2. Wednesday, March 14, 2012
  3. 2012-03-14
  4. 3/14/12
  5. 03/14/12
  6. 14-Mar-12
  7. March 14, 2012
  8. 3/14/2012
  9. 14-Mar-2012
English (India)
  1. 14-03-2012
  2. 14 March 2012
  3. 14-03-12
  4. 14-3-12
  5. 14.3.12
  6. 2012-03-14
English (United Kingdom)
  1. 14/03/2012
  2. 14 March 2012
  3. 14/03/12
  4. 14/3/12
  5. 14.3.12
  6. 2012-03-14

English (Canada)

  1. 2012-03-14
  2. March 14, 2012
  3. 14-03-2012
  4. 14-03-12
  5. 14-3-12
  6. 12-03-14
  7. 3-14-12
  8. 14-Mar-12

English (Australia)

  1. 14/03/2012
  2. Wednesday, 14 March 2012
  3. 14/03/12
  4. 14/3/12
  5. 14/3/2012
  6. 14-Mar-12
  7. 14-March-2012
  8. 2012-03-14
  9. 12/03/14
  10. 2012/03/14
  11. 14 March 2012

German (Germany)

  1. 14.03.2012
  2. Mittwoch, 14. Marz 2012
  3. 2012-03-14
  4. 14.3.12
  5. 14.03.12
  6. 14. Mrz. 12
  7. 14. Mrz 12
  8. 14. Marz 2012
  9. 14.3.2012
  10. 14. Mrz. 2012

German (Switzerland)

  1. 14.03.2012
  2. Mittwoch, 14. Marz 2012
  3. 14.03.12
  4. 14. 3. 12
  5. 14.3.12
  6. 2012-03-14
  7. 14. Marz 2012
  8. 14. Mar 12

German (Austria)

  1. 14.03.2012
  2. Mittwoch, 14. Marz 2012
  3. 14.03.12
  4. 14.3.2012
  5. 2012-03-14
  6. 14.Marz 2012
  7. 14.Mar2012
  8. 14 Mar 2012

Norwegian Nynorsk (Norway)

  1. 14.03.2012
  2. onsdag 14. mars 2012
  3. 14.03.12
  4. 14.3.12
  5. 2012-03-14
  6. 14. mars 2012

Russian (Russia)

  1. 14.03.2012
  2. 14 mapta 2012 r.
  3. 2012-03-14
  4. 14.3.12
  5. 14.03.12
  6. 14 map 12
  7. 2012, 14 mapta
  8. 14.3.2012
  9. 14-map-2012

Korean (South Korea)

  1. 2012-03-14
  2. 1900년 8월 21일 화요일
  3. 1900년 8월 21일
  4. 00年 8月 21日
  5. 12-3-14
  6. 12/3/14
  7. 2012-3-14
  8. 2012/3/14
  9. 3/14/12
  10. 03/14/12
  11. 14-Mar-12

Portuguese (Brazil)

  1. 14/03/2012
  2. quarta-feira, 14 de marco de 2012
  3. 2012-03-14
  4. 14/3/12
  5. 14/03/12
  6. 14-mar-12
  7. 21/ago/00

Portuguese (Portugal)

  1. 14/03/2012
  2. 14 de marco de 2012
  3. 2012-03-14
  4. 14-03-2012
  5. 14/3/12
  6. 14/03/12
  7. 14-mar-12
  8. 14/3/2012
  9. 14-mar-2012

Italian (Italy)

  1. 14/03/2012
  2. mercoledi 14 marzo 2012
  3. 2012-03-14
  4. 14/3/12
  5. 14/03/12
  6. 14-mar-12
  7. 14 marzo 2012
  8. 14/3/2012
  9. 14-mar-2012

French (France)

  1. 14/03/2012
  2. mercredi 14 mars 2012
  3. 2012-03-14
  4. 14/3/12
  5. 14/03/12
  6. 14-mars-12
  7. 14 mars 2012
  8. 3/14/2012
  9. 14-mars-2012

French (Canada)

  1. 14 mars 2012
  2. 2012-03-14
  3. 12-03-14
  4. 14-03-12
  5. 12 03 14
  6. 14 mars, 2012

Spanish (United States)

  1. 3/14/2012
  2. miercoles, marzo 14, 2012
  3. 3/14/12
  4. 03/14/12
  5. 03/14/2012
  6. 12/03/14
  7. 2012-03-14
  8. 14-mar-12
  9. marzo 14, 2012
  10. miercoles, 14 marzo, 2012
  11. 14 marzo, 2012

Spanish (Mexico)

  1. 14/03/2012
  2. miercoles, 14 de marzo de 2012
  3. 14/03/2012
  4. 14/03/12
  5. 14/3/12
  6. 14-03-12
  7. 2012-03-14
  8. miercoles 14 de marzo de 2012
  9. 14 de marzo de 2012
  10. 21-ago-00

Spanish (Spain)

  1. 14/03/2012
  2. miercoles, 14 de marzo de 2012
  3. 2012-03-14
  4. 14-3-12
  5. 14-03-12
  6. 14-mar.-12
  7. 14 de marzo de 2012
  8. 14-3-2012
  9. 14-mar.-2012

Chinese (Simplified, China)

  1. 2012/3/14
  2. 2012-03-14
  3. 1900年8月21日
  4. 12/3/14
  5. 3/14/12
  6. 03/14/12
  7. 14-Mar-12
  8. 一九○○年八月二十一日 (August 21, 2000)

Chinese (Traditional, Taiwan)

  1. 2012/3/14
  2. 2012-03-14
  3. 1900年8月21日
  4. 一九○○年八月二十一日 (August 21, 2000)
  5. 3/14/12
  6. 03/14/12
  7. 14-Mar-12

Arabic (Saudi Arabia)

  1. 14/06/12
  2. 2012-06-14
  3. 14/6/2012
  4. 2012/06/14
  5. 21/August/1900
  6. ٢١/٠٨/١٩٠٠
  7. ١٩٠٠/٠٨/٢١

Japanese (Japan)

  1. 2012/03/14
  2. 2012/3/14
  3. 2012-03-14
  4. 1900年8月21日
  5. 3/14/12
  6. 03/14/12
  7. 14-Mar-12

Hebrew (Israel)

  1. 14/03/2012
  2. 2012-03-14
  3. 14/3/12
  4. 14/3/2012
  5. 14.3.12
  6. 14.3.2012
  7. יום שלישי 21 אוגוסט 1900
  8. 3/14/2012
  9. 21 אוג 00 (dd mm yy)
  10. 21 אוגוסט 1900
  11. 14 Mar 12
  12. 14 March 2012
  13. 21-אוג-00 (dd-mmm-yy)

Dutch (Netherlands)

  1. 14-3-2012
  2. woensdag 14 maart 2012
  3. 2012-03-14
  4. 14-03-12
  5. 14-mrt-12
  6. 14 maart 2012
  7. 3-14-2012
  8. 14-mrt-2012
Swedish(Sweden)
  1. 2012-03-14
  2. den 14 mars 2012
  3. 12-03-14
  4. 14-3 2012
  5. 14-3 -12
  6. 14-3 12
  7. 14-3-12
  8. 14 mars 2012
  9. 14 mars -12
  10. 2012 03 14
  11. 21-ago-00
Finnish (Finland)
  1. 14.3.2012
  2. keskiviikko 14. maaliskuu 2012
  3. 14.3.12
  4. 14. maaliskuuta 12
  5. 14. maaliskuuta 2012
  6. 2012-03-14

How to Use Dates

Once you've configured the Date data type, you can input dates in the corresponding cells by manually entering them or choosing from the date picker by double clicking on any Date type cell.

Dates-DatePicker.jpg

In the date picker, the current date is highlighted with a blue border, and the cell date is highlighted with a blue fill. From the top of the date picker, the single arrows will advance the view by month, and the double arrows will advance the view by year. Click the blue Today button at the bottom of the date picker to automatically select the current date.

Conversion Behavior

When converting existing data in cells and columns to the Date data type, Spreadsheet.com will attempt to match the data to date formats like converting "01/01/2020" to "January 1, 2020".

Spreadsheet.com assumes the same locale as the worksheet's creator. So, for example, for a user in the United States, 01/02/2020 would be converted to "January 2, 2020", whereas, for a user in the United Kingdom, 01/02/2020 will be converted to "1 February 2020". Before converting dates, you may want to ensure the entered data is consistent with your desired locale formatting.

If you are interested in including the time in your data, consider using the Date & time data type or check out our Duration data type for calculating the number of days between dates. Read on to learn more about the rest of Spreadsheet.com's Data Types.