如何转换这种格式为"20180101"的字符串?转换为日期值，例如01/01/2018在Excel中? [英] How to a convert a string in this format "20180101" into a date value like 01/01/2018 in Excel?

问题描述

我可以使用什么公式将字符串转换为将显示为01/01/2018的日期值?

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

推荐答案

如果，您尝试将其从公式移到另一个单元格中，而不是在原位进行(为此，请使用文本列"(根据Jeeped的回答)，您可以将DATE和MID组合在一起，也可以使用REPLACE并用+0或DATEVALUE进行转换:

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,"-"))

(其中A1是要转换的日期)

(Where A1 is the date to convert)

第一个公式只是将数字切成2018 01 01并将其用作年，月和日.第二个2通过首先在位置7("201801-01")然后在位置5("2018-01-01")插入连字符(即REPLACE 0个字符)并将字符串转换回数字/日期来工作.

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.

这篇关于如何转换这种格式为"20180101"的字符串?转换为日期值，例如01/01/2018在Excel中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！