Excel: 将文本字符串转换为日期

Question

我有一个可行的公式可以将以下文本转换为日期。然而，我不明白为什么必须添加末位0才能以YYYY格式显示年份。

21.04.2016 转换为 4/21/2016

=(MID(A2,4,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,2))+0

这很简单明了。但是，如果您在公式结尾处删除0，则只会显示16而不是2016。但我可以用Right(A2,4)代替Right(A2,2)。但我仍然想知道为什么？有人知道吗？谢谢！

- purpleblau

---

你好奇是怎么发现添加零会起作用的！！;) - SrinR

事实是，这段代码不是我写的。我在网上看到过它，只是不理解它背后的原因。 - purpleblau

在Excel中，将一个零添加到一个字符串可以将其转换为数字，适当的格式化可以使Excel将该数字显示为日期。 - Gary's Student

@Gary'sStudent 你太快了！去当地的咖啡店/甜甜圈店奖励一下自己吧！ - Forward Ed

谢谢解释。所以，如果我们在公式中添加一个0，它会自动将字符串转换为数字，无论如何？ - purpleblau

@lulumink 只在某些情况下使用。 - Gary's Student

Answer 1

末尾的零将整个内容转化为数学运算，导致加号左边的字符串被视为数字。

你也可以使用*1代替+0。

=(MID(A2,4,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,2))*1

或者您可以在括号前面加上--，然后删除+0，这样也能实现同样的功能。

=--(MID(A2,4,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,2))

- Forward Ed

---

现在，变得清楚了！非常感谢！如果我们将一个数学运算符添加到公式中，Excel会将其视为数字而不是其他任何内容。有道理！ - purpleblau

Answer 2

正如Ed所回答的，这是因为它将您的日期字符串视为数字。

然而，Excel必须解释该字符串以获取其真正的数字，并且为此它依赖于区域设置。在美国区域设置下，您的公式非常有效，但是当我将其插入到英国区域设置的excel中时，由于“04/21/16”在英国不是有效的日期或数字，因此会得到#Value。

为了避免这个问题，您应该使用DATE()函数将其转换为日期，这将不受区域设置的影响。

=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

- Phil

---

感谢Phil提供的替代方案！也非常好用！ - purpleblau

这是更好的答案，避免依赖本地设置来消除日期的歧义。 - chris neilsen

认同这是更好的方法将日期转换，并完全支持它作为获取从字符串到Excel日期值（阅读upvote）的答案，但技术上问题本质上是“+0在做什么？” 但那可能有点吹毛求疵。 - Forward Ed

使用DATE()函数的另一个优点是，单元格将自动采用合理的格式。 - Gary's Student

@ForwardEd 我完全同意这不是真正的问题，但问题的标题呼喊着需要这个澄清，而我没有评论权限。 - Phil