一步将日期中的小时和分钟删除 [英] Remove hours and minutes from dates in one step

问题描述

我总是得到日期，如 A列中所示，但是我需要如C列所示的日期。如何创建而不需要创建 B列帮助器的日期？我需要这样做，因为小时和分钟弄乱了我想在此之后实现的目标。

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 B helper? I need that because the hours and minutes mess up what I want to achieve after this.

A列

小时和分钟的时间：

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

列C

没有小时和分钟的时间：

Time without hours and minutes:

22/05/2015 
11/06/2015 
28/05/2015 
26/05/2015
25/05/2015
25/05/2015

我设法通过创建列B来解决此问题，该列B转换为文本

I managed to solve this by creating a column B that converts to text

=Text(A2;"DD-MM-AAAA")

，然后列C将文本转换为值

and then column C converts text to value

=Data.value(B2)

虽然此过程有效，但它却使我产生了很多不必要的数据，我想知道是否可以使用a vba-excel 宏。我还希望C列中的日期始终等于 A列中的日期，并且日期顺序相同。

While this process works, it causes me a lot of unnecessary data and I wonder if I can do this automatically with a vba-excel macro in one step. I also want the number dates in column C always equal to the number of dates in column A and dates in the same order.

推荐答案

这是用[excel-vba]标记的，因此我将提供完整的列时间剥离解决方案。

This was tagged with [excel-vba] so I'll offer a full column time stripping solution.

Range.TextToColumns方法使剥离日期时间中日期部分的工作变得快速。 fieldinfo 参数可以指定您正在使用的DMY格式，并使用适当的 xlColumnDataType 。虽然大多数 TextToColumns命令都是就地执行的，但如果指定了替代的目的地，则可以使用。指定备用目的地时，原始数据保持不变。

The Range.TextToColumns method makes quick work of stripping off the date portion of a datetime. The fieldinfo parameter 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 destination is available if specified. When specifying an alternate destination, the original data is left intact.

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

处理后，您将在A列中保留原始日期时间，并保留真实日期-o C列中的nly值。

After processing you are left with your original datetimes in column A and true date-only values in column C.

< img src = https://i.stack.imgur.com/1a4St.png alt = strip_date_out_of_datetime>

这篇关于一步将日期中的小时和分钟删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！