How to a convert a string in this format “20180101

问题:

This question already has an answer here:

• Changing YYYYMMDD to MM/DD/YYYY 5 answers

What formula can I use to transform the string into a date value that will appear as 01/01/2018?

回答1:

If you are trying to do this from a formula into another cell, and not in situ (For which, use "Text to Columns" as per Jeeped's answer), you can either combine DATE and MID, or use REPLACE and convert with +0 or DATEVALUE:

=DATE(MID(A1,1,4), MID(A1,5,2), MID(A1,7,2))

OR

=REPLACE(REPLACE(A1,7,0,"-"),5,0,"-")+0

OR

=DATEVALUE(REPLACE(REPLACE(A1,7,0,"-"),5,0,"-"))

(Where A1 is the date to convert)

The first formula just cuts the number up into 2018 01 01 and uses those as Year, Month and Day. The second 2 work by first Inserting (i.e. REPLACE 0 characters) a hyphen at position 7 ("201801-01") and then at position 5 ("2018-01-01") and converting the string back to a number/date.

回答2:

Use Data, TextToColumns, Fixed width, Date: YMD, Finish. Possibly Date: YDM depending on your string date format (you provided an ambiguous example).

回答3:

use the code below

=RIGHT((text),2)&"/"&MID((text),5,2)&"/"&LEFT((text),4)

回答4:

Use the DATEVALUE() function which takes as input a text format. Not that if your output cell is not in Date format you will see a simple int insteade of the expected 1/1/2016.

I refer you to the DATEVALUE function documentation for more insight on the input format and so on