Google-sheets – Date fields that refuse to be parsed/formatted

conditional formattingformulasgoogle sheetsgoogle-sheets-datesgoogle-sheets-import-csv

I have some very mysterious date fields in a huge (600 000 records) CSV file which absolutely refuses to behave.

They come up aligned left, and conditional formatting shows that they cannot be seen as dates for some reason, even though they are formatted the same as all the other dates — d/m/Y.

What's more, these same dates are the ones giving me trouble when importing users into WordPress using wp-import. In their user profiles, they show today's date. About half the dates are doing this.

Any light that could be shed on this would be much appreciated.

UPDATE: Here is a link to a sheet with a sample of date fields.

Best Answer

d/m/Y is an ambiguous date formatting.

It's very likely that this occurs because the spreadsheet regional setting used in the spreadsheet doesn't support d/m/Y as a date formatting. Please note that the screenshot included in the question shows column J values having a "day" value greater than 12, with exception of 11/01/2019 have the a white cell background instead of green.

NOTE: It's very likely that the other values aren't correctly parsed as the date they represents, i.e. 09/03/2014 is very likely that is parsed as September 3rd, 2014 instead of March 9th, 2014

One quick solution is to set the spreadsheet regional setting to one that supports d/m/Y before adding the values to the spreadsheet.

Formatting Google Sheet Dates Inconsistent

Related Solutions

Google Sheets – Import CSV and Show Formatted Hyperlinks

I've been searching the internet and found out that there's no way to do that in Google Spreadsheet. I did however created a fairly simple work-a-round:

Prepare the following CSV line:
test,'=&*HYPERLINK(C1;E1),www.google.com,"Search Google"
Go to the Edit menu and select Find & Replace:
Enter the following:
The formula will be recognised and accepts the references.

Adding the special characters (&*) will make it very unlikely that other "fields" are affected by the "Find & Replace" function.

Google-sheets – Highlight cells whose content cannot be parsed as number

Use conditional formatting with a custom formula. For example, select cell A1, go to Format/Conditional formatting, select "custom formula" and enter

=ISERROR(VALUE(A1))

In the same dialog you can choose the range to which this formatting should be applied.

Best Answer

Related Solutions

Google Sheets – Import CSV and Show Formatted Hyperlinks

Google-sheets – Highlight cells whose content cannot be parsed as number

Related Topic