1

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.

Improve this question
0
1

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.

Related

Improve this answer
-1

  • if this is just for conditional formatting purposes, use custom formula:

    =COUNTIF(DATE(RIGHT(A1; 4); MID(A1; 4; 2); LEFT(A1; 2)); "<"&TODAY())

  • otherwise highlight the whole problematic column

  • copy it CTRL + C
  • go to custom date formatting and setup a custom format dd/MM/yyyy

  • and then paste the column over with CTRL + SHIFT + V

Improve this answer
4
  • Tearing my hair out because the second solution above works fine on the pared down sheet but not on the full one.
    – Sharkfin
    Feb 21 '19 at 22:55
  • you can try to change locale settings in your spreadsheet because it looks like that Argentina spreadsheet uses mm/dd/yyyy format as default. eg. you can try for example Czech locale where a default is dd/mm/yyyy if you need it so badly in this format :)
    – user0
    Feb 21 '19 at 23:08
  • I have solved the problem using excel. Still very curious as to why Sheets was behaving so strangely for me but don't have the time to dwell on it. Thanks again Mark!
    – Sharkfin
    Feb 23 '19 at 9:37
  • @BenAmato webapps.stackexchange.com/help/someone-answers
    – user0
    Feb 23 '19 at 9:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.