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 到 （日期） 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.

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