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. Do I need to use regex? How do I accomplish this?
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.
(standard American date format)
From Google Support:
If you want a proper date (recommended) please try:
Custom Format as Number
mm/dd/yyyy to show leading
If you want a text string to include leading
0s please try:
Here is the formula for the one of you trying to convert a date were the 0 would have been removed:
=date(right(J5,4),if(mid(J5,2,1) = "/",left(J5,1),left(J5,2)),if(mid(J5,3,1) = "/",mid(J5,4,if(mid(J5,5,1) ="/",1,2)),if(mid(J5,2,1) = "/",mid(J5,3,if(mid(J5,5,1) = "/",2,1)))))
In this case I'm going from American to European: M/d/yyyy, MM/d/yyyy, MM/dd/yyyy or M/dd/yyyy and convert it to dd/MM/yyyy
You can set your sheet location in sheet settings (File → Sheet settings). Your sheet will show you the right format for you.
Click the gear icon on the right from your main Google Drive dashboard and select Settings. If you don't have a language selected you can set it here. This should apply to all future documents.
Either you can change the default calendar setting for google doc as follows:
File > Spreadsheet settings > Change locale or Timezone