formulasgoogle sheetsgoogle-sheets-dates
I generated a report that formats date as 20160509 and my Google Sheets locale is already set to United States. I've played around with various options within Format > Number > Date Format but I can't change the dates to MM/DD/YYYY format without doing a simple find and replace. How do I accomplish this?
It appears that the hours' field is required for time to be recognized as such. Enter 0:mm:ss.SSS (assuming decimal dot; could be a comma in your locale), for example 0:22:22.222
The default format for duration does not include the milliseconds' field, eg. use a custom one:
The first field should be "elapsed" to make the format a duration format: e.g., 70 minutes will stay 70 minutes without carrying over to hours.
I did not realize there was a "Format > Number > More formats > More date n Time formats" where I could specify the exact format I want, since the default date format is not what I am looking for.
It also handles auto-formatting of further inputs
Best Answer
Assuming your string is in cell A1, this formula will convert it to a date. You can then format the date however you prefer.
Or, take the leftmost four characters as the year, the rightmost two characters as the day, and two characters in the middle starting at position 5 as the month, and convert it to a date.
turns into
(standard American date format)
From Google Support: