如何一步 Excel-VBA 从日期中删除小时和分钟
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37144005/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to remove hours and minutes from dates in one step Excel-VBA
提问by carlos_cs
I always get the dates like shown in Column A, but I need them as shown in column C. How can I do it without having to create column Bhelper? I need that because the hours and minutes mess up what I want to achieve after this.
我总是得到A 列中所示的日期，但我需要 C列中所示的日期。如何在不必创建B 列助手的情况下做到这一点？我需要它，因为时间和分钟搞乱了我在此之后想要实现的目标。
Column A
A栏
Time with hours and minutes:
带小时和分钟的时间：
22/05/2015 14:57
11/06/2015 15:40
28/05/2015 08:27
26/05/2015 14:51
25/05/2015 14:18
25/05/2015 15:51
Column C
C栏
Time without hours and minutes:
没有小时和分钟的时间：
22/05/2015
11/06/2015
28/05/2015
26/05/2015
25/05/2015
25/05/2015
I managed to solve this by creating a column B that converts to text
我设法通过创建一个转换为文本的 B 列来解决这个问题
=Text(A2;"DD-MM-AAAA")
and then column C converts text to value
然后 C 列将文本转换为值
=Data.value(B2)
While this process works, it causes me a lot of unnecessary data and I wonder if I can do this automatically with a vba-excelmacro in one step. I also want the number dates in column C always equal to the number of dates in column Aand dates in the same order.
虽然这个过程有效，但它给我带来了很多不必要的数据，我想知道我是否可以在一个步骤中使用vba-excel宏自动完成此操作。我还希望 C 列中的日期数始终等于A 列中的日期数和相同顺序的日期数。
回答by Scott Craner
To get the date only use:
要仅获取日期，请使用：
=INT(A2)
And format to date. This works because dates in excel are the number of days since December 31st, 1899 so for example May 10th, 2016 is 42500 days since.
并格式化为日期。这是有效的，因为 excel 中的日期是自 1899 年 12 月 31 日以来的天数，因此例如 2016 年 5 月 10 日是 42500 天以来。
And the Time is a decimal based on 24 hours. So, May 10th, 2016 12:00:00 equates to 42500.5.
时间是基于 24 小时的小数。因此，2016 年 5 月 10 日 12:00:00 等于 42500.5。
So to get just the date we remove the decimal. INT() does just that.
所以为了得到日期，我们删除了小数点。INT() 就是这样做的。
回答by Scott Craner
This was tagged with [excel-vba] so I'll offer a full column time stripping solution.
这被标记为 [excel-vba] 所以我将提供一个完整的列时间剥离解决方案。
The Range.TextToColumns methodmakes quick work of stripping off the date portion of a datetime. The fieldinfoparameter can specify the DMY format you are using and discard the time portion with the appropriate xlColumnDataType. While most TextToColumns commands are made 'in place', an alternate destinationis available if specified. When specifying an alternate destination, the original data is left intact.
该Range.TextToColumns方法，使剥离日期时间的日期部分快的工作。该字段信息参数可以指定要使用的DMY格式，并丢弃适当的时间部分xlColumnDataType。虽然大多数 TextToColumns 命令是“就地”制作的，但如果指定了备用目标，则可用。指定备用目的地时，原始数据保持不变。
Option Explicit
Sub stripTime()
Dim i As Integer
With Worksheets("Sheet1")
'check to see if A1 is a date or a column text label
i = Abs(Not IsDate(.Cells(1, 1).Value))
With .Range(.Cells(1 + i, "A"), .Cells(.Rows.Count, "A").End(xlUp))
.TextToColumns Destination:=.Cells(1, "C"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, xlDMYFormat), Array(10, xlSkipColumn))
'optionally assign a custom number format
.Offset(0, 2).NumberFormat = "[color10]dd-mmm-yyyy_)"
'optionally autofit the column width
.Offset(0, 2).EntireColumn.AutoFit
End With
End With
End Sub
After processing you are left with your original datetimes in column A and true date-only values in column C.
处理后，您将在 A 列中保留原始日期时间，在 C 列中保留真实的仅日期值。
?????????
?????????
回答by Rtronic
you can use =Today()OR =NOW()then change
您可以使用=Today()OR =NOW()然后更改
