We help IT Professionals succeed at work.

removing all time stamps from the dates throughout the column in each cell

Patricia Timm
Patricia Timm asked
on
I have the following in my cell 7/12/2019 11:32
Each cell contains different dates and time stamps
I would like to get rid of the timestamp in every cell and just keep the date. I dont want to have to type in each date
Thanks in advance
Comment
Watch Question

View Solutions Only

Consultant
CERTIFIED EXPERT
Commented:
Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
You could also use the integer function =INT(number), and format the result as a date.
Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
Lastly you could use Text to Columns and use a "space" as the delimter.
Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
You could also use Power Query to change it on an ongoing basis in the event your need is a continuing issue.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Try this macro.

Sub RemoveTime()

Dim cel As Range
Dim wsS As Worksheet
Dim strParts() As String

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
End With

Set wsS = ActiveSheet

With wsS
For Each cel In .UsedRange.Cells
Debug.Print cel
    strParts = Split(cel, " ")
    If UBound(strParts) = 2 Then
        If IsDate(strParts(0)) Then
            cel = strParts(0)
            cel.NumberFormat = "m/d/yyyy"
        End If
    End If
Next
End With
With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With
End Sub

Open in new window

Patricia Timm

Author

Commented:
Thanks!
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
              Experts Exchange Top Expert VBA 2018, 2019
              Experts Exchange Distinguished Expert in Excel 2018