从yyyyymmdd到mm/dd/yyyy的Excel数据转换

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

我已经搜索了一个小时关于如何在Excel中做这个。

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.

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

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.

我尝试过创建自定义格式，但我不确定我是否做对了。我发现有几个地方他们想走另一条路去yyyyyyyyyyyyyyyyyyyyymmdd，但是他们没有帮助。

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

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

I have two separate columns that need to be changed.

我有两个独立的列需要修改。

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

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

4 个解决方案

#1

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

Where A1 is the field you need to convert.

其中A1是需要转换的字段。

Alternatively, you could use this code in VBA:

或者，您可以在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.

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

#2

Do you have ROWS of data (horizontal) as you stated or COLUMNS (vertical)?

是否有如您所述的行(水平)或列(垂直)?

If it's the latter you can use "Text to columns" functionality to convert a whole column "in situ" - to do that:

如果是后者，你可以使用“文本到列”的功能来转换整个列“原位”——这样做:

Select column > Data > Text to columns > Next > Next > Choose "Date" under "column data format" and "YMD" from dropdown > Finish

选择列>数据>文本到列>下一个>下一个>从下拉到>结束，在“列数据格式”和“YMD”下选择“日期”

....otherwise you can convert with a formula by using

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

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

=文本(A1,“0000-00-00”)+ 0

and format in required date format

并以所需日期格式进行格式

#3

Here is a bare bones version:

这里有一个简简单单的版本:

Let's say that you have a date in Cell A1 in the format you described. For example: 19760210.

假设在单元格A1中有一个日期，格式是您描述的。例如:19760210。

Then this formula will give you the date you want:

然后这个公式会告诉你你想要的日期:

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

On my system (Excel 2010) it works with strings or floats.

在我的系统(Excel 2010)上，它可以处理字符串或浮点数。

#4

for converting dd/mm/yyyy to mm/dd/yyyy

用于将dd/mm/yyyy转换为mm/dd/yyyy。

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

#1