How can I enter a date in the format I choose?

Question

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.

Answer 1

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.

Answer 2

If you must enter 210916 instead of 21/09/16 (which will be recognised as a date), then you will need to parse your data entry value into components and then combine them into a date using the =DATE(y, m, d) function.