15

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?

enter image description here

1
  • 2
    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, 2016 at 19:20

6 Answers 6

17

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:

5

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)
1

This is what I use in my helper column using the inbuilt google sheets function:

=text(TO_DATE(DATEVALUE(A1)),"MM/DD/YYYY")

This is also what I use when I need put a date string as the part of a filename:

The function datevalue() accepts any date string format including the excel raw date numbers that you have on your 1st column. TO_DATE() converts the datevalue() to something lawyers can read. The outer function Text(,"") allows you to modify the date format.

I use it to return integers that can be used in formulas dealing with dates on google sheets.

The how: Those raw numbers on your 1st column? 1 is January 1st 1900. 2 is January 2, 1900 ..and so on...

In google sheets, each cell on your column is equal to the number of days after December 30, 1899, [post-gilded age, when the U.S. became a corporate state under admiralty law and issues public policies instead of common law, meaning no one is supposed to harm you unless you agree to waiving your rights].

The function to_date()converts that raw date value to a format more recognizable. The function is equivalent to clicking on the taskbar Format > Number > Date > time from the menu bar.

As a side note: The function =Text(TO_DATE(DATEVALUE(A1)),"MM-DD-YYYY)" in a helper column is what I use to convert any date string into a string format usable for windows file names since / is not accepted for windows file naming. No need to worry if the date has a missing leading zero.

1
  • This answer was marked as low quality. Please consider to add a brief explanation about how this formula answers the question.
    – Rubén
    Apr 30, 2021 at 2:30
0

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

0

You can set your sheet location in sheet settings (FileSheet settings). Your sheet will show you the right format for you.

-3

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

3
  • 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, 2016 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, 2016 at 18:55
  • Yep, tired that too and keep on getting unexpected output :/
    – lineplay
    Oct 13, 2016 at 19:15

Your Answer

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

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