Date

  • Updated

The Date data type mceclip0.png 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.

mceclip2.png

Use the Date data type whenever you need to set the date. For instance, 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.

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

Topics in this Article

 

Formatting

The Date data type has particular formatting options that change depending on the location you set, as noted below.

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 & Locale (location)

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 our other 27 languages/locales. 

Each locale has its own format types to correspond to that country and language; the animation below shows how these options change as you switch locales in the Date dialog box.

Date_Formats.gif

A list of some of the current formatting options, by locale, is shown in the table below. 

  Locale (Location) Date Formats
1 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
2 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
3 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
4

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
5

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
6

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
7

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
8

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
9

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
10

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
11

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
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
13

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
14

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
15

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
16

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
17

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
18

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
19

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
20

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)
21

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
22

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. ١٩٠٠/٠٨/٢١
23

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
24

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)
25

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
26 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
27 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

The Date Picker

After setting your column, cell, or range of cells to the Date data type, you will have the ability to type in dates or select from the date picker. When you double-click on any Date type cell, you will see the below date picker (though without these included instructions).

mceclip4.png

Converting Data to the Date data type

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

But Spreadsheet.com does assume the same locale as the worksheet's creator. So, for instance, 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 wish to ensure the entered data is consistent with your desired locale formatting.

If you are interested in including the time in your data, check out our Date & time data type. Or, check out our Duration data type for calculating the number of days between dates.