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

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

CodePudding user response：

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

CodePudding user response：

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);
}