Google Sheet wrong date and time format for Timeline Graph

Question

I import Tasker data source into a Google Sheet, but Timeline Graph does not work.

The format of the Date and Time is not understood.

The format of my data source is dd-MM-YYYY HH.mm For example: 16-10-2020 00.40

But Google Sheet understands only dd/MM/YYYY HH:mm:ss 16/10/2020 00:35:00

See here (On the second picture, I modified the date to be 16/10/2020 00:35:00, and applied Date and Time format, so it display 16-10-2020 00.35, which serves no purpose other than confusing you)

I've tried to change the format of the date, but the only thing it does is: Convert an already existing 16/10/2020 00:35:00 format To 16-10-2020 00.40

I want the opposite.

I'm tired and kind of angry at Google Devs. I'm been searching for hours, and I find it crazy Google Sheet cannot understand this simple format.

Please any idea?

Thank you

Answer 1

Please use the following formula to convert your values to dates

=ARRAYFORMULA(IF(A2:A5="",,--REGEXREPLACE(REGEXREPLACE(A2:A5,"-","/"),".",":")))

Depending on your locale you may have to change , to ;

=ARRAYFORMULA(IF(A2:A5="";;--REGEXREPLACE(REGEXREPLACE(A2:A5;"-";"/");"\.";":")))

Functions used:

• ArrayFormula
• REGEXREPLACE

Answer 2

An alternative to the solution already provided

Google Sheets Formulae

You can use this formula in order to replace the . with : and drag it down in order for it to be applied to all the cells:

=REPLACE(A1,14,1,":")

This will result in a column with the date formatted in the way you want it.

Google Apps Script

You can also make use of Apps Script in order to replace all the . with : directly in the sheet.

// Copyright 2020 Google LLC.
// SPDX-License-Identifier: Apache-2.0

function toDate() {
  var ss = SpreadsheetApp.openById("SS_ID").getSheetByName("SHEET_NAME");
  var range = ss.getRange(1,1,NO_OF_ROWS,1);
  var values = range.getValues();
  for (let i=1; i<values.length; i++)
    ss.getRange(i, 1).setValue(new Date(values[i-1][0].toString().replace(/\./g, ":")));
}

The script above also sets the value to be of type date.

Reference

• REPLACE formula Google Sheets;

• Google Apps Script.