EXCEL VBA CSV 日期格式问题

EXCEL VBA CSV Date formatting issue

提问by Simon

I am an Excel VBA newbie. My apologies if I am asking what seems to be an obvious question. I have a CSV text file I am opening in an Excel worksheet. One row of the file contains date information formatted as "YY-MMM', (ie: 9-FEB, or 10-MAY). When the Excel spreadsheet opens the file the date information is changed to "mm/dd/yyyy" format and reads as 02/09/2009 or 05/10/2009 where the YY-MMM is now MM/YY/2009, (ie: the 9-FEB becomes 02/09/2009, and the 10-MAY becomes 05/10/2009).

我是 Excel VBA 新手。如果我问的是一个显而易见的问题，我深表歉意。我在 Excel 工作表中打开了一个 CSV 文本文件。文件的一行包含格式为“YY-MMM”的日期信息（即：9-FEB 或 10-MAY）。当 Excel 电子表格打开文件时，日期信息将更改为“mm/dd/yyyy”格式并读取为 02/09/2009 或 05/10/2009，其中 YY-MMM 现在是 MM/YY/2009，（即：9-FEB 变为 02/09/2009，10-MAY 变为 05/10 /2009）。

I would like to know if it is possible to reformat the field from YY-MMM to mm/01/yyyy.

我想知道是否可以将字段从 YY-MMM 重新格式化为 mm/01/yyyy。

I have tried to parse the date field after converting it to text with

我试图在将日期字段转换为文本后解析它

Range("B11", "IV11").NumberFormat = "text"

However, then the value is a serial date and non-parsable.

但是，该值是一个序列日期并且不可解析。

I have been unsuccessfully looking for a list of the NumberFormat options.

我一直在寻找 NumberFormat 选项的列表，但没有成功。

If you can point me in a direction it will be much appreciated.

如果你能指出我的方向，我将不胜感激。

回答by Simon

Just to answer a part of your question, here is the list of date formatting options (excluding time):

只是为了回答您的问题的一部分，这里是日期格式选项列表（不包括时间）：

d = day of month, e.g. 7
dd = zero-padded day of month, e.g. 07
ddd = abbreviated day name, e.g. Thu
dddd = full day name, e.g. Thursday

pretty much the same for month...

一个月几乎一样...

m = month number, e.g. 7
mm = zero padded month number, e.g. 07
mmm = abbreviated month name, e.g. Jul
mmmm = full month name, e.g. July

years are simpler...

年更简单...

yy = 2 digit year number, e.g. 09
yyyy = 4 digit year number, e.g. 2009

you can combine them and put whatever separators you like in them

您可以组合它们并在其中放入您喜欢的任何分隔符

e.g.

例如

YY-MMM, 09-FEB
DDDD-DD-MMM-YY, Wednesday-04-Feb-09
dd/mm/yyyy, 04/02/2009
mm/dd/yyyy, 02/04/2009

I've just been trying a few things out and I think your best bet is to change the format in the text file to conform to a more standard date arrangement. If you can reverse them (e.g. MMM-YY) you'll be fine, or split them into separate columns (what if when you import you define - as a separator as well as comma?). This is one case where Excel trying to be clever is a pain.

我刚刚尝试了一些东西，我认为最好的办法是更改文本文件中的格式以符合更标准的日期安排。如果您可以反转它们（例如 MMM-YY），您就可以了，或者将它们拆分为单独的列（如果您在导入时定义 - 作为分隔符和逗号会怎样？）。在这种情况下，Excel 试图变得聪明是一种痛苦。

HTH

HTH

回答by barrowc

If you are currently using File > Opento open the CSV file then try Data > Import External Data > Import Datainstead. This brings up the text import wizard which might give you more flexibility in how to import the file. Specifically, it lets you declare a column in the file as being text so that Excel does not try to parse the value

如果您当前正在使用File > Open打开 CSV 文件，请Data > Import External Data > Import Data改为尝试。这会打开文本导入向导，它可以让您更灵活地导入文件。具体来说，它允许您将文件中的一列声明为文本，以便 Excel 不会尝试解析该值

As Simon has explained, your terminology for the current date format is not correct. 9-FEBcorresponds to d-mmmformat and 02-19-2009(NB deliberately changed to an unambiguous date for this example; 02-09-2009 is 9th Feb in the US but 2nd Sept in the UK) corresponds to mm-dd-yyyy

正如西蒙所解释的，您当前日期格式的术语不正确。9-FEB对应于d-mmm格式和02-19-2009（注意在这个例子中故意更改为一个明确的日期；02-09-2009 在美国是 2 月 9 日，在英国是 9 月 2 日）对应于mm-dd-yyyy

If you wanted to change the NumberFormatto text for the range starting in cell B11 and ending in cell IV11 then you would use:

如果您想更改NumberFormat从单元格 B11 开始到单元格 IV11 结束的范围的文本，那么您可以使用：

Range("B11:IV11").NumberFormat = "@"

Range("B11:IV11").NumberFormat = "@"

@ signifies text format and you need the : operator to indicate a contiguous range of cells. Your original example of Range("B11","IV11")actually indicates the union of cells B11 and IV11 and thus would only have affected two cells

@ 表示文本格式，您需要 : 运算符来表示连续的单元格范围。您的原始示例Range("B11","IV11")实际上表示单元格 B11 和 IV11 的联合，因此只会影响两个单元格

It is unusual to have your data structured in rows rather than columns. Rather than having your dates in row 11, it would be more common to have them in column K instead so that you would use Range("K2:K65535")or just Rows("K")Most of the built-in Excel stuff like Sort and AutoFilter assume that your data is laid out in columns anyway. Row 1 traditionally contains the column names and then each subsequent row contains an individual record. You might want to look at how your CSV file is being generated to see if you can switich it to a more usable column-based structure

以行而不是列来组织数据是不寻常的。不是将日期放在第 11 行，而是将它们放在 K 列中更常见，这样您就可以使用Range("K2:K65535")或仅使用Rows("K")大多数内置 Excel 内容（如 Sort 和 AutoFilter）假设您的数据无论如何都排列在列中. 传统上，第 1 行包含列名，然后每个后续行包含一个单独的记录。您可能想查看 CSV 文件的生成方式，以查看是否可以将其切换为更有用的基于列的结构

If you have a date like 19-FEBas text in a cell then, assuming that you always want the year part to be the current year, you can change it to the first day of the month in mm/dd/yyyyformat in VBA. This example only changes one cell but you can use it as the basis for a wider solution:

如果您19-FEB在单元格中有一个类似文本的日期，那么假设您始终希望年份部分是当前年份，您可以mm/dd/yyyy在 VBA 中以格式将其更改为月份的第一天。此示例仅更改一个单元格，但您可以将其用作更广泛解决方案的基础：

Option Explicit

Sub main()

Dim strOrigValue As String

With ThisWorkbook.Worksheets("Sheet1").Range("B11")
    strOrigValue = .Value

    .NumberFormat = "mm/dd/yyyy"
    .Value = DateSerial(Year(Date), Month(strOrigValue), 1)
End With

End Sub

DateSerialcreates a date serial number from the given year, month and day. Year(Date)extracts the year part from the current system date, Month(strOrigValue)takes the month part from the data that's already in the cell and we want the first day of the month

DateSerial从给定的年、月和日创建一个日期序列号。Year(Date)从当前系统日期中提取年份部分，从Month(strOrigValue)单元格中已有的数据中提取月份部分，我们想要月份的第一天

回答by Kimball Robinson

In order to read in a date, you can do something like this:

为了读取日期，您可以执行以下操作：

myDate = DateValue(dateText)

although there are caveats to how excel may interpret dates, and what technical limits it has in what dates it may store. (you may need to read up on maximum and minimum dates, and how excel interprets two-digit years >30 vs <30, among other things)

尽管对于 excel 如何解释日期以及它可以存储的日期有哪些技术限制存在一些警告。（您可能需要阅读最大和最小日期，以及 excel 如何解释两位数的年份 >30 和 <30，等等）

In order to format the date, you can use one of three functions:

为了格式化日期，您可以使用以下三个函数之一：

myDateText = DatePart("yyyy", myDate) & "/" & DatePart("mm", myDate) & "/" & DatePart("dd", myDate)

or this

或这个

Format (myDate, "yyyy/mm/dd")

or this

或这个

FormatDateTime(#1/1/2020#, vbShortDate)

You can search google for more documentation on each function. For the format function, you will want to find a good reference -- I've seen some rather incomplete ones. MSDN tends to have good ones, but not always. Feel free to bump this answer if you want more details.

您可以在 google 上搜索有关每个功能的更多文档。对于格式功能，您需要找到一个很好的参考资料——我看过一些相当不完整的参考资料。MSDN 往往有好的，但并非总是如此。如果您想要更多详细信息，请随意提出这个答案。

回答by JeffO

This assumes your 2 digit year is 2000. Once you end up with your incorrect date 9-Feb = 02/02/2009, you can convert this to the date you want with the formula: =DATE(Day(B11)+2000,Month(B11),1)

这假设您的 2 位数年份是 2000。一旦您得到错误的日期 9-Feb = 02/02/2009，您可以使用以下公式将其转换为您想要的日期：=DATE(Day(B11)+2000,月(B11),1)