Google Sheets keep Time format with Datetime data

Question

Details

In my Google Sheet I have a column that has been formatted in the 03:59 PM format. When I insert time into it, the data is correctly formatted as 00:00 AM automatically, but not with the correct date, as it says it's from the year 1899.

When I insert data and specify the date first, it overwrites the columns 00:00 AM format and instead inserts a MMM DD YYYY 00:00 format with the month being text, day and year in numbers, and no AM or PM. When I manually format the cell (or whole column) again with 03:59 PM format, it shows the right time format and has the correct date.

I'm currently adding data via a Python script with the Gspread module. This allows me to format it with a specified pattern. The data is added to a cell as a date time string, which then changes the cell's formatting, but then immediately after that individual cell is overridden again into a hh:mm AM/PM pattern format: {"numberFormat": {"type": "DATE_TIME", "pattern": "hh:mm AM/PM"}}.

Therefore right now only when manually typing in data, it isn't overridden immediately after like the Python script.

Question

How can I insert data into a sheet that has a date time property (or at least contain both the date and time), while not overwriting the column's 00:00 AM formatting?

Have tried:

• Formatting the entire column and then entering data
• Entering data with various formats, like 12/12/2021 3:00 PM which adds the PM but still overwrites just the time format.

Answer 1

Here is the rather clumsy solution, but probably it will work for you:

function onEdit(e) {
  if (e.range.columnStart != 2) return; // if it's not column B do nothing

  var value = e.range.getDisplayValue();
  if (value.length < 12 && value.length > 0) {
    var tz = Session.getTimeZone();
    var today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');
    var date = Utilities.formatDate(new Date(today + ' ' + value), tz, 'yyyy-MM-dd hh:mm a');
    e.range.setValue(date);
    e.range.setNumberFormat('hh:mm am/pm');
  }
}

It sets current year, month and day for any 'date' that is shorter than 12 letters (it supposes that this 'date' is a time).

Probably you want to move the last line e.range.setNumberFormat('hh:mm am/pm'); down, out of {}. In this case it will format all inputted 'dates' as time hh:mm.

function onEdit(e) {
  if (e.range.columnStart != 2) return; // if it's not column B do nothing

  var value = e.range.getDisplayValue();
  if (value.length < 12 && value.length > 0) {
    var tz = Session.getTimeZone();
    var today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd a');
    var date = Utilities.formatDate(new Date(today + ' ' + value), tz, 'yyyy-MM-dd hh:mm a');
    e.range.setValue(date);
  }
  e.range.setNumberFormat('hh:mm am/pm');
}

Update

Here is a little bit less clumsy variant of the same solution:

function onEdit(e) {
  if (e.range.columnStart != 2) return; // if it's not column B do nothing

  var date = e.range.getValue();

  if (date.getFullYear() < 1990) {
    var time = e.range.getDisplayValue();
    var tz = Session.getTimeZone();
    var today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd a');
    var new_date = Utilities.formatDate(new Date(today + ' ' + time), tz, 'yyyy-MM-dd hh:mm');
    e.range.setValue(new_date);
  }

  e.range.setNumberFormat('hh:mm am/pm');
}

It changes year, month, day to current date if the automatically assigned year less than 1990.

Answer 2

Clear B2:B and in B2:

={A2:A}

You can then enter the full date in A2:A and see expected formatting in B2:B

• Raw data: A2:A
• Presentation: B2:B

You can also separate raw data and presentation to separate sheets.