从 yyyymmdd 转换为 mm/dd/yyyy 的 Excel 日期转换

Question

我已经花了大约一个小时来查找如何在Excel中完成这个任务。

我有一个从旧系统创建的Excel文件，正在从SQL Server数据库中提取信息，我将把这些信息输入回SQL Server数据库，并希望日期匹配。

我尝试创建自定义格式，但我不确定我是否做得正确。我发现有几个地方想要将日期格式从 mm/dd/yyyy 改为 yyyymmdd，但它们并没有帮助到我。

我不熟悉在任何Microsoft产品中使用VBA，否则我相信这将是一个简单的任务。

我有两个需要更改的不同列。

如何将整个列从(float)yyyymmdd格式更改为(Date)mm/dd/yyyy格式？

- Malachi

---

右键单击行，然后单击“格式单元格”。在“数字”选项卡下，单击“自定义”。在那里，您可以将其设置为“mm/dd/yyyy”。 - Siddharth Rout

我尝试过那个方法，但它只是插入了“#################……” - Malachi

好的，那我需要看一下你的Excel文件。你能在www.wikisend.com上传几行样本数据并在这里分享链接吗？ - Siddharth Rout

当Malachi试图将该数字显示为日期时，Excel会尝试将其转换成日期格式...根据提供的数字，可能会得出一个大约50-60万年后的日期。我认为这个数值太大了。 - Daniel

顺便提一下，显式为日期的单元格（Excel 2010）中可以显示的最大日期是2958465，即公元9999年12月31日。 - Daniel

Answer 1

你可以使用类似这样的公式将值转换为日期，并将其放在单元格旁边：

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

A1是需要转换的字段。

或者，您可以在VBA中使用以下代码：

Sub ConvertYYYYMMDDToDate()
   Dim c As Range
   For Each c In Selection.Cells
       c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
       'Following line added only to enforce the format.
       c.NumberFormat = "mm/dd/yyyy"
   Next
End Sub

只需突出显示您想要固定的任何单元格，然后运行代码。

请注意，正如RJohnson在评论中提到的那样，如果您所选的单元格中有一个为空，则此代码将出错。您可以添加一个关于c.value值的条件来跳过更新，如果它为空的话。

- Daniel

---

好的！以下是将YYYYMMDDHHMMSS格式转换为日期格式的公式：=DATE(LEFT(B1,4),MID(B1,5,2),MID(B1,7,2))，它会保持原意并使内容更加通俗易懂。 - emragins

你介意把这个运行一下，从YYYYMMDD到DD/MM/YYYY吗？ - Gaia

@Gaia 为此，您需要将数字格式更新为“dd/mm/yyyy”。即 c.NumberFormat = "dd/mm/yyyy"。 - Daniel

@Gaia。有点像。让日期以不同的方式显示只有两种方法。你必须手动更改单元格的格式（这就是代码选项所做的），或者更新操作系统中的默认日期格式。 - Daniel

@RJohnson 没错，我添加了一个关于空单元格的注释。 - Daniel

Answer 2

您是否有行数据（水平）如您所述，还是列数据（垂直）？

如果是后者，则可以使用“文本分列”功能在原地将整个列转换-要执行此操作：

选择列> 数据> 文本分列> 下一步> 下一步> 在“列数据格式”下选择“日期”和“YMD”> 完成

...否则，您可以通过使用公式进行转换

=TEXT(A1,"0000-00-00")+0

并按所需的日期格式进行格式化

- barry houdini

---

你的答案也是正确的！我实际上在寻找@Leo和@DanielCook回答的内容。但“文本分列”是我遇到的问题的最佳解决方案。可能不像编写一个公式并将其复制粘贴到其他单元格或运行宏那样快，但是它是一个非常好的解决方案！我希望每个人都能点赞这个答案！ - Malachi

这似乎是实际的答案，因为我们不需要任何公式在这里，整个列可以用这个方法转换。感谢您提供这个答案。 - sunil

从我记得的来看，@sunil，我实际上尝试过这个方法，但它会不正确地更改列中的数据，这就是为什么我没有采用这种方式的原因。我接受了那个真正为我的问题提供解决方案的答案，请给予那个答案应有的赞。 - Malachi

这个答案实际上是错误的，我想要从 YYYYMMDD 转换成 Excel 可以识别的日期格式，而不是相反。 - Malachi

TEXT函数将简单地将数据转换为文本值，例如20180130转换为2018-01-30。如果您想要一个真正的日期，则可以使用+0进行转换，然后以任何您想要的方式格式化日期。任何不改变值的操作都可以使用，例如*1。 - barry houdini

Answer 3

以下是一个简单的版本：

假设您在A1单元格中有一个以您描述的格式表示日期。例如：19760210。

那么这个公式将为您提供所需的日期：

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)).

在我的系统上（Excel 2010），它适用于字符串或浮点数。

- Leo

Answer 4

将 dd/mm/yyyy 转换为 mm/dd/yyyy

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

- user2027618

---

这不是我所要求的，但我可以看出它对其他人会很有用，所以我点了赞！ - Malachi

Answer 5

我在这里找到另一个（手动）方法，对我很管用：

1. 选择需要处理的列。
2. 点击数据选项卡。
3. 点击文本到列 - 弹出新的窗口。
4. (选择分隔符), 下一步。
5. (取消全部勾选, 使用 "无" 作为文本限定符), 下一步。
6. 使用日期下拉菜单中的 ymd 选项。
7. 点击完成。

- rwb