Date formats

The spreadsheet convertor makes an effort to process dates properly.

  • Google Sheets, Excel, and LibreOffice (soffice) all do different things to recognise date formats.

  • When downloading a Google Sheet with rclone, it will be converted into Excel, with sometimes unexpected conversions.

  • When using soffice to convert to CSV, separate flag can indicate whether to use the shown value or the typed value. This improves the ability to convert.

  • For some US-formatted dates (mm/dd/yyyy) it would be possible to detect its use (for days above 12, which cannot be months). This is not implemented.

An overview of various formats in Google Sheets and the resulting value used for conversion.

Table 1. Icons used in Table 2.

⚠️

A value format that is recognised and interpreted.

Short years: values up to 70 will be considered to be in the 21st century, 20xx, from 70 onwards to be 20th century, 19xx).

⚠️⛔️

A value that will either be interpreted incorrectly, or result in a date error.

US-formatted days up to 12 will be converted without warning, and the month and day will be reversed.

⛔️

A value that will result in a date error.

✅️

A value that will be correctly processed.

Table 2. Date format conversions
Google sheets version date input Description soffice format after download with rclone soffice export with "Save cell contents as shown" default true soffice export when using "Save cell contents as shown" false

28-10

generic

date, format dd—​mm

⛔️ 28-10

✅️ 28/10/2021

16/05/2020

string

number, general

✅️ 16/05/2020

✅️ 16/05/2020

17/05/2020

copy of above, edited to 17

date, format dd/mm/yyyy

✅️ 17/05/2020

✅️ 17/05/2020

28-10-21

short year

date, format dd—​mm-yy

⚠️ 28-10-21

✅️ 28/10/2021

28-10-2021

long year

date, format dd-mm-yyyy

✅️ 28-10-2021

✅️ 28/10/2021

10-28-2021

US, default

number, general

⛔️ 10-28-2021

⚠️⛔️ 10-28-2021

10-28-2021

US, formatted as date

date, format mm-dd-yyyy

⛔️ 10-28-2021

⚠️⛔️ 10-28-2021

12-10-2021

US, ambiguous date

date, format mm-dd-yyyy

⛔️ 12/10/2021

✅️ 10/12/2021

28/10

date with slash, day+month

date, format dd/mm

⛔️ 28/10

✅️ 28/10/2021

28/10/21

date with slash, short year

date, format dd/mm/yy

⚠️ 28/10/21

✅️ 28/10/2021

28/10/2021

date with slash, long year

date, format dd/mm/yyyy

✅️ 28/10/2021

✅️ 28/10/2021

10/28

US with slash, day+month

number, general

⛔️ 10/28

⛔️ 10/28

10/28/21

US with slash, short year

number, general

⛔️ 10/28/21

⚠️⛔️ 10/28/21

10/28/2021

US with slash, long year

number, general

⛔️ 10/28/2021

⚠️⛔️ 10/28/2021

10/28

US with slash, day+month

date, format mm-dd

⛔️ 10/28

⛔️ 10/28

10/28/21

US with slash, short year

date, format mm-dd-yyyy

⛔️ 10/28/21

⚠️⛔️ 10/28/21

10/28/2021

US with slash, long year

date, format mm-dd-yyyy

⛔️ 10/28/2021

⚠️⛔️ 10/28/2021

28-7

short month (no leading 0)

date, format dd—​mm

⛔️ 28-7

✅️ 28/07/2021

7-28

short month, US

number, general

⛔️ 7-28

⛔️ 7-28

7-28-2021

short month + year, US

number, general

⛔️ 7-28-2021

⚠️⛔️ 7-28-2021

5-6-2021

error prone: can be US or not, here: not

date, format dd-mm-yyyy

✅️ 5-6-2021

✅️ 05/06/2021

6-5-2021

error prone: can be US or not, here: US

date, format mm-dd-yyyy

⛔️ 6-5-2021

✅️ 05/06/2021

31-6-2021

false date

date, format dd-mm-yyyy

⛔️ 31-6-2021

⛔️ 31-6-2021

29-2-2021

false leap year date

number, general

⛔️ 29-2-2021

⛔️ 29-2-2021

Twitter LinkedIn Github Gitlab Meetup RSS Miro Trello Zotero Hypothesis