CODEWITHSUNDEEP
dategoogle-sheetsdate-conversion
Macterror
Macterror

Reputation: 431

Convert 7 or 8 digit number to true date

I have a column full of dates in this format mmddyyyy and I was wondering if I could parse or convert it to mm/dd/yyyy. I tried selecting the entire row and then clicking on format -> number -> date but when I do this, a date such as 12082016 becomes 6/7/34979 which of course makes no sense at all. Any ideas on how to fix this?

Edit:

Examples:

 7072016   12/11/2021
 1202017    12/2/2017
10042016    10/4/2016
 9212016    8/12/2023

I changed the B to F as that is where the date column is. What is strange is that some of the values are correct

=date(right(B2, 4), left(B2, 2), mid(B2, 3, 2))

Upvotes: 1

Views: 6200

Answers (4)

Max Makhrov
Max Makhrov

Reputation: 18717

Try this formula using regular expressions:

=TEXT(REGEXREPLACE(TEXT(A1,"00000000"),"(\d{2})+(\d{2})+(\d{4})","$1/$2/$3"),"m/d/yyyy")

Change m/d/yyyy to any date format you need.

Upvotes: 0

Rajesh Sinha
Rajesh Sinha

Reputation: 197

Date & Time values are basically series of digits, that's why it always converts it to either Date or Time, for which is formatted. Bttr use Custom Format,, or Change the Default as u need. If you want to convert any numbers into date,, First write 1st date of the year convert it into number,, get the Trend,, write nXt values,, & convert into an appropriate format.

Upvotes: 0

Roger Sinasohn
Roger Sinasohn

Reputation: 483

If you're using Google Sheets, you'll need to format the column with the number in it as 00000000 to ensure there are a full 8 digits, including leading zeroes for months less than 10. To do this, highlight the column with the MMDDYYYY numbers then from the Format menu, select Number then Custom Number Format:

The Custom Number Format option on the Google Sheets Format Menu

Then, in the dialog box that comes up, enter 00000000 (eight zeroes) and click Apply:

Google Sheets' Custom Number Format Dialog

Your data will be reformatted and your dates should be correct:

The resulting dates are correct when the MMDDYYYY numbers are zero-filled.

Hope this helps!

Upvotes: 3

user4039065
user4039065

Reputation:

You have to account for both 7 and 8 digit numbers. Apparently, you lost some leading zeroes when importing your data.

=date(right(right("0"&A2, 8), 4), left(right("0"&A2, 8), 2), mid(right("0"&A2, 8), 3, 2))
' or,
=--replace(replace(right("0"&A2, 8), 5, 0, "/"), 3, 0, "/")

Format the cells as mm/dd/yyy or however you wish; They are true dates now.

enter image description here

Upvotes: 5

Related Questions