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?

enter image description here

  • 1
    The problem is really that Sheets isn't recognizing that as a date. I don't think you can simply format that into a date format you like; you'll need a "helper" column to transform the string into a date format Sheets recognizes. – ale Oct 13 '16 at 19:20
up vote 6 down vote accepted

Assuming your string is in cell A1, this formula will convert it to a date. You can then format the date however you prefer.

=date(left(A1,4),mid(A1,5,2),right(A1,2))

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.

19961210

turns into

12/10/1996

(standard American date format)


From Google Support:

If you want a proper date (recommended) please try:

=date(left(C2,4),mid(C2,5,2),right(C2,2))

Custom Format as Number mm/dd/yyyy to show leading 0s.

If you want a text string to include leading 0s please try:

=mid(C2,5,2)&"/"&right(C2,2)&"/"&left(C2,4)

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.

enter image description here

Either you can change the default calendar setting for google doc as follows:

Under

File > Spreadsheet settings > Change locale or Timezone

  • Thanks for helping but I generated a report that formats date as 20160509 and my Google Sheets locale is already set to United States. – lineplay Oct 13 '16 at 18:48
  • Follow the following menu list: Format > Number > More Format(at bottom) > More date and time format. Now you can add delete each thing separately. Hope it helps. – Amit Jha Oct 13 '16 at 18:55
  • Yep, tired that too and keep on getting unexpected output :/ – lineplay Oct 13 '16 at 19:15

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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