I have been using google drive and google spreadsheet for a while. I work with experts around the world and we all use different date format. The biggest confusion which often happens when we are sharing data is a timestamp. For some people it is natural to have dates time like dd/mm/yyyy and for some it is natural to have mm/dd/yyyy. Sometimes we are just confused looking at the date as there is no instruction about the format. Finally, we all decided that we will spell out the dates. For example, if we have date like 11/9/2014 to avoid confusion if it is November 9, 2014 or September 11, 2014 we decided to just write it in the string.
Now here is the problem with Google Spreadsheet – when I type any datetime, like as following, it will immediately convert them to mm/dd/yyyy format and defeat our purpose of spelling that out.
Finally, we figured out the solution how we can preserve the datetime as a string in Google Spreadsheet. Just start your datetime with a single quotes. For example now we enter datetime as follows:
‘September 9, 2014 instead of September 9, 2014. The best part is that, the single quotes are not visible on the screen.
Well, if there was any other way to do this and I was not aware of it, please let me know.
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
A single quote prefix is a standard way to force text data when entering data into a spreadsheet cell. So technically you are entering a date string and not a date value. :-)
This behavior is not unique to Sheets, Excel is the same. The downside is that the underlying data is now string typed. You can format using the format menu. Of course if you will be converting the data anyway this point is moot…
Why not just use a Date format (Month Day, Year) for display?
Menu options:
Format / Number / More Formats / More Date and time formats / (Month Day, Year)
That way you can enter it any way you like and end result is still the same format?
@Mark: The problem is that mm/dd/yyyy is not a standard format across the globe. Hence, a date like 05/01/2014 may cause a confusion between May 01, 2014 and January 05, 2014.
Better to spell it out for clarity and global understanding (especially, when the audience may be those who are unaware of the mm/dd/yyyy format).