CODEWITHSUNDEEP
excelvbadate
carlos_cs
carlos_cs

Reputation: 124

Remove hours and minutes from dates in one step

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.

Column 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

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 

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

and then column C converts text to value

=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 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.

Upvotes: 6

Views: 35183

Answers (4)

Ahmet Hayrullahoglu
Ahmet Hayrullahoglu

Reputation: 950

10-04-2020 3:09:42 PM transforms to 10.04.2020

Using this formula:

=IF(ISBLANK(A1),"",TEXT(A1,"DD.MM.YYYY"))

Upvotes: 0

Rtronic
Rtronic

Reputation: 673

you can use =Today() OR =NOW() then change

Upvotes: 0

user4039065
user4039065

Reputation:

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

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

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

         strip_date_out_of_datetime

Upvotes: 4

Scott Craner
Scott Craner

Reputation: 152450

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.

And the Time is a decimal based on 24 hours. So, May 10th, 2016 12:00:00 equates to 42500.5.

So to get just the date we remove the decimal. INT() does just that.

Upvotes: 22

Related Questions