0

I am trying to format a list of dates in google sheets. One of the dates are 20161001, I highlight the cell and go up to Format-> Number -> More Formats -> More Date and Time Formats and choose DD MMM YY format. The the cell changes to 10 Dec 98. As you can see once formatted the date is incorrect.

The original date is pull from an Excel spreadsheet out of a system.

0

The problem is that Sheets is not recognizing that number as a date. It sees 20161001 as 20,161,001 which, when converted to days from the "epoch" date1, gets you December 10, 1998.

You need to convert that string into an actual date that Google recognizes.

If your value is in cell A1, this will do it:

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

The date() function takes numeric values as arguments for year, month, day. The left(), mid(), and right() functions take the various pieces of that numeric string. Once converted, you'll get a date that makes sense and can apply any date format you like.


1 December 31, 1899

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