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.

2 Answers 2


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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.