Google-sheets – How to enter a date in the format I choose

formattinggoogle sheets

I am trying to create a spreadsheet with 5 sheets, there is going to be a date entered on sheet1, which will have a date+1 for the sequential days, i.e enter Monday's date and the following dates will enter correctly into the later weekdays.

I am running into a problem where I've tried to format the cell as a date, but whatever date I enter it just has a 'random' one instead, like it's reading my input incorrectly.

Whats the best way I can have a cell that I can enter 210916 as the date, it will auto-format to 21/09/16 or 21-09-16 and the formula for the sequential days on the other sheets? I keep getting number/text coercion issues as well.

Best Answer

The cell format only dictates how the date will be displayed, it does not say anything about which input format is recognized as a date.

You have some influence over the input format when specifying the spreadsheet's locale, but you'll probably not be able to enter 210916 and have it recognized as a date.

See How can I change the input format for dates in Google Docs' spreadsheet? for how to change locale.

Related Solutions

Google-sheets – Auto date insert when opposite cell is populated

This answer is coming late to the party, but you can achieve what you want by installing this script:

function onEdit(event) {
  var eventRange = event.range;
  if (eventRange.getColumn() == 4) { // 4 == column D
    var columnARange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 1, eventRange.getNumRows(), 1);
    var values = columnARange.getValues();
    for (var i = 0; i < values.length; i++) {
       values[i][0] = new Date();
    }
    columnARange.setValues(values);
  }
}

EDIT: It seems you just want to trigger the script when column D is modified, so I have modified the code accordingly.

To install it, go to Tools -> Script editor, paste the code above, and save. From then on, every time you edit a cell in column D, the current timestamp will be inserted in column A.

Google-sheets – Formatting Google Sheet Dates Inconsistent

Spreadsheet

The spreadsheet demo locale linked on the question is set to United States which use month-day-year sequence for dates. The value shown on the video is 25/03/2015 as the month valid values are from 1 to 12, it can´t be interpreted as a date value on the original spreadsheet.

It's very likely that the second spreadsheet, that converts the 25/03/2015 to date, has a locale that use day-month-year sequence for dates.

Looks that we can't be sure that "ambiguous" strings like 4/3/2015 are being converted to the correct date.

Google Forms

Google Forms responses could be exported as a CSV file from the Google Form. At this time dates are being included in the CSV file as "yyyy-mm-dd", so could be more reliable to use the exported CSV file than the responses sent directly to a the original spreadsheet.

See View and manage form responses for details about how to download forms responses al CSV file.