vba 从日期中删除存储在数组中的整个数据的时间

Removing time from date for an entire data stored in an array

提问by absundo

Using Excel VBA, how would I apply the formula Date(year(A1),Month(A1),Day(A1))to all the dates in an entire column? Currently the data is in date time format (e.g. 30/04/1995 9:35:00 AM) but I want just the date - 30/04/1995.

使用 Excel VBA，如何将公式Date(year(A1),Month(A1),Day(A1))应用于整列中的所有日期？目前数据采用日期时间格式（例如 30/04/1995 9:35:00 AM），但我只想要日期 - 30/04/1995。

Currently all of the dates are stored in an array and I have tried Columns(data_column).NumberFormat = "[$-1009]d-mmm-yy;@", but I have not been successful in removing the time from the dates.

目前所有的日期都存储在一个数组中，我已经尝试过Columns(data_column).NumberFormat = "[$-1009]d-mmm-yy;@"，但是我没有成功地从日期中删除时间。

回答by xidgel

If you want to change the display format of your data while preserving the time information, you can use something like NumberFormat = "mm/dd/yyyy" in cells of a worksheet or chart axis.

如果要在保留时间信息的同时更改数据的显示格式，可以在工作表或图表轴的单元格中使用类似 NumberFormat = "mm/dd/yyyy" 的内容。

Excel stores date/time info as a floating point number. The days are to the left of the decimal and fractions of days (hh:mm:ss) are to the right. If you want to strip out the time information from the underlying data, then convert to Long then back to Date. In VBA (thanks to T.M. for the correction):

Excel 将日期/时间信息存储为浮点数。天数在小数点的左边，天数的分数 (hh:mm:ss) 在右边。如果您想从底层数据中去除时间信息，则先转换为 Long，然后再转换回 Date。在 VBA 中（感谢 TM 的更正）：

DateOnly = CDate(Int(DateTime))

In worksheet formulas just convert to Int and format as you please

在工作表公式中，只需根据需要转换为 Int 和格式

DateOnly = INT(DateTime)

Hope that helps

希望有帮助

回答by Ralph

All dates in Excel include time: datetime. It is not possible to store a date only or a time only in a cell. The reason is that Excel stores them as numbers. Anything before the decimal point is the date and anything after the decimal point is the time. So, even if you put the number 42000 in a cell (without anything after the decimal point) and change the format of that cell to date, the value will still be December 27, 2014 (42000 days after December 31, 1899) with an assumed time of zero = 00:00:00 in the morning.

Excel 中的所有日期都包含时间：datetime。不可能在单元格中仅存储日期或时间。原因是 Excel 将它们存储为数字。小数点前是日期，小数点后是时间。因此，即使您将数字 42000 放入单元格中（小数点后没有任何内容）并将该单元格的格式更改为日期，该值仍将是 2014 年 12 月 27 日（1899 年 12 月 31 日之后的 42000 天），并带有假定时间为零 = 早上 00:00:00。

Since all numbers can potentially have something after the decimal point, all dates have time in Excel and all times have dates.

由于所有数字都可能有小数点后的内容，因此所有日期在 Excel 中都有时间，所有时间都有日期。

The only thing you can do is: format a cell to show only the date part or the time part or both. So, all you need to do is to hide the time.

您唯一可以做的是：格式化单元格以仅显示日期部分或时间部分或两者。所以，你需要做的就是隐藏时间。

If you want to change all dates to have zero after the decimal point then you'll have to loop through the numbers and change all values to INT values.

如果要将所有日期更改为小数点后为零，则必须遍历数字并将所有值更改为 INT 值。

For intColumn = 1 to 1000
   If Sheet1.Cells(1, intColumn).Value2 = vbNullString then Exit For
   Sheet1.Cells(1, intColumn).Value2 = Int(Sheet1.Cells(1, intColumn).Value2) ' Do not use CInt but Int only!
next intColumn

回答by T.M.

Removing time from date for an entire data stored in an array

从日期中删除存储在数组中的整个数据的时间

IMO this question rather asks how to change values directly in an arraycontaining datesincluding time indications. Therefore I the following example code just as an addition to the valid solutions above demonstrating how to

IMO 这个问题而是询问如何直接更改包含日期（包括时间指示）的数组中的值。因此，我将以下示例代码作为上述有效解决方案的补充，演示如何

• get a zero-based 1-dim array from a data column (without loops),
• execute the time removals (in a Loop) using VBA's Fixfunction *) to cut the decimal part of the date value and
• (optionally write back the array to any wanted column)

• 从数据列中获取一个从零开始的一维数组（无循环），
• 使用VBA的Fix函数 *)执行时间删除（在循环中）以削减日期值的小数部分和
• （可选地将数组写回任何想要的列）

*) Using the CLnginstead of Fix(or Int) would round up to the next day any dates with afternoon hours after midday.

*) 使用CLng而不是Fix（或Int）会将中午之后的下午时间的任何日期四舍五入到第二天。

Example code

示例代码

Assumes date values e.g. in column B:B(omitting a title row).

假定日期值，例如列B:B（省略标题行）。

Sub chngDateArray()
  Dim ws As Worksheet  
  Set ws = ThisWorkbook.Worksheets("MySheetName")                             ' << change to your sheet name string
  Dim lastRow&, i&, arr
  lastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row     
' [1a] assign dates to (1-based) 2-dim datafield array
       arr = ws.Range("B2:B" & lastRow).Value2                                ' get date values as numbers
' [1b] rearrange to (0-based) 1-dim array
       arr = Application.Transpose(arr): ReDim Preserve arr(0 To UBound(arr) -1) ' make it a "flat" (zero-based) array
' [2]  eliminate time portions and reconvert to date
       For i = 0 To UBound(arr)    
           arr(i) = CDate(Fix(arr(i)))                                        ' cut time portions from entire dates
       Next i
       ' Debug.Print Join(arr, "|")                                           ' check in immediate window

' [3]  {optional}: write back array to any wanted column (e.g. D:D)
       ws.Range("D2").Resize(UBound(arr), 1).Value2 = Application.Transpose(arr)
End Sub

Additional note

附加说明

Using .Value2in section [1a]allows to get dates as numbers only. The reconversion to date format in section [2]eventually allows to write back the array data to any wanted column in section [3]without prior column formattingvia NumberFormat.

使用.Value2in 部分[1a]允许仅以数字形式获取日期。在节中重新转换为日期格式[2]最终允许将数组数据写回到节中任何想要的列，[3]而无需通过NumberFormat.

回答by Marcucciboy2

In addition to the above, you should also be able to pull just the date from those strings using either DateValue()or Format()like so:

除上述外，你也应该能够拉只是来自使用这些字符串的日期DateValue()或Format()像这样：

DateValue("30/04/1995 9:35:00 AM")
Format("30/04/1995 9:35:00 AM", "dd/mm/yyyy")