How can I force data input into cells in Google Sheets to be in a desired date format?

Question

I have a column of cells set to use a specific datetime format. However, when a user inputs something else, like 1 or 5/5 into one of the cells, it overrides the formatting setting and takes the input as-is. In Excel, if I use that cell formatting and then input a 1, it changes it to 01/01/1900 00:00:00. Is there a way to make Google Sheets do the same thing?

I can use data validation to make sure the input is a legitimate date, but it doesn't actually format it the way I need, it just gets rid of some really bad inputs, which aren't even my main concern.

I know there are regedit-based ways to get dates the way you need, but the ones I've seen require a separate input and result column, which doesn't work for our end-users just entering data in the one column.

Answer 1

It is a good question; I don't know if it can be achieved within Google Sheets.

You could use a script though: open 'Apps Script' in the 'Extensions' menu, and replace all the "myFunction" stuff with the following code:

function onEdit(e) {
  const columnLetter = "G";
  const colNum = SpreadsheetApp.getActiveSheet().getRange(`${columnLetter}1`).getColumn();
  const cell = e.range;
  if (cell.getColumn() === colNum) {
    cell.setNumberFormat("dd/mm/yyyy hh:mm");
  }
}

You must modify two things:

1. replace the column letter (G in my example) with that of your specific column, leaving in the quotation marks - so "B";, not B; ;
2. replace the date-time format (the "dd/mm/yyy hh:mm" part) with your desired one. Here, I put a 25/09/2024 01:02 format, but you can custom that using the following guide: Date & number formats. Once again, leave the quotation marks.

That ought to do what you want: whenever a user edits any cell in the columnLetter column, the script will force the format.