Excel 日期从 yyyymmdd 到 mm/dd/yyyy 的转换 [英] Excel Date Conversion from yyyymmdd to mm/dd/yyyy

问题描述

关于如何在 Excel 中执行此操作，我已经搜索了大约一个小时.

I have been searching for about an hour on how to do this in Excel.

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

I have an Excel file that was created from an old system and I am pulling information from a SQL Server Database, I will be inputting the information back into the SQL Server Database and would like the Dates to match.

我尝试过创建自定义格式，但我不确定我是否正确地做到了.我找到了几个地方，他们想从 mm/dd/yyyy 到 yyyymmdd 的另一种方式，但他们没有帮助.

I have tried Creating a Custom Format, but I am unsure if I even did it Correctly. I found several places where they want to go the other way mm/dd/yyyy to yyyymmdd but they have not been helpful.

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

I am unfamiliar with using VBA in any Microsoft Products otherwise I am sure that this would be a simple Task.

我有两个单独的列需要更改.

I have two separate columns that need to be changed.

如何将整列从 (float)yyyymmdd 格式化为 (Date)mm/dd/yyyy

How do I Format the entire column from (float)yyyymmdd to a (Date)mm/dd/yyyy

推荐答案

您可以使用这样的公式将值转换为日期，在单元格旁边:

You can convert the value to a date using a formula like this, next to the cell:

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

其中 A1 是您需要转换的字段.

Where A1 is the field you need to convert.

或者，您可以在 VBA 中使用此代码:

Alternatively, you could use this code in 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

只需突出显示您想要修复的任何单元格并运行代码.

Just highlight any cells you want fixed and run the code.

请注意评论中提到的 RJohnson，如果您选择的单元格之一为空，则此代码将出错.您可以在 c.value 上添加一个条件，如果它为空则跳过更新.

Note as RJohnson mentioned in the comments, this code will error if one of your selected cells is empty. You can add a condition on c.value to skip the update if it is blank.

这篇关于Excel 日期从 yyyymmdd 到 mm/dd/yyyy 的转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！