'Google Sheets - DATE format not working on imported Date in TEXT format

I text based .csv file with a semicolon separated data set which contains date values that look like this

When I go to Format>number> I see the Google sheets has automatic set. In this state I cannot use and formulas with this data.

I go to Format>number> and set this to date but formulas still do not see the actual date value and continue to display an error

Can someone share how I can quickly activate the values of this array so formulas will work against them? I would be super thankful

Solution 1:^[1]

Where the date are in column A, starting in cell A1, this formula will convert to DATE as a number, after which you apply formatting to Short Date style.

=ARRAYFORMULA(IF(A1:A="",,DATE(RIGHT(A1:A,4),MID(A1:A,4,2),LEFT(A1:A,2))))

Solution 2:^[2]

Hopefully(!) the dates stay as text, otherwise Google Sheets would sometimes detect MM/dd/yyyy instead of dd/MM/yyyy, and you won't be able to distinguish between July 9th and September 7th in your example.

Solution #1

If your locale is for instance FR, you can then apply

=arrayformula(if(A1:A="";;value(A1:A)))

solution#2

you can try/adapt

function importCsvFromIdv1() {
  var id = 'the id of the csv file';
  var csv = DriveApp.getFileById(id).getBlob().getDataAsString();
  var csvData = Utilities.parseCsv(csv);
  csvData.forEach(function(row){
    date = row[0]
    row[0] = date.substring(6,10)+'-'+date.substring(3,5)+'-'+date.substring(0,2)
  })
  var f = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  f.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

Solution 3:^[3]

First thanks to those that suggested a fix. I am not really a programmer and get cold sweats when I see suggesting of running scripts to solve simple problems. Sorry guys.

So the (non programmer) solution with the dates was to do a find/replace (CTRL + H) and replace all the (.)dots with (/)slashes, then to make sure the column is formatted as a date, then Google finally understands it as a date.

With the accounting values as well, I had to do the same find/replace to remove all the ' between thousands, then google woke up and understood them as numbers.

I am significantly underwhelmed by this from Google. They are getting too fat and lazy. They need some competition.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Mr Shane
Solution 2
Solution 3	Mastercore

'Google Sheets - DATE format not working on imported Date in TEXT format

Solution 1:[1]

Solution 2:[2]