Stripping Time Stamp away from Date

[Guzzlr]

Hello
I have column F with the typical Date and Time, which begins in cell (F6).
I need to take away the time stamp in Column F all the way to the bottom of F where there is data, so just the date is showing.
I found the below code on google, and I am trying to modify it so it begins in F6 and strips the time stamp from the date to the bottom of the last cell with data in column F.

Sheets("Report Paste").Select
With Selection
    i = Abs(Not IsDate(.Cells(6, 6).Value))
    With .Range(.Cells(1 + i, "F"), .Cells(.Rows.Count, "F").End(xlUp))
        .TextToColumns Destination:=.Cells(1, "F"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, xlDMYFormat), Array(10, xlSkipColumn))
    End With
End With

Can I get some help on why this is not working.
Thank you for the help

 

[Guzzlr]

@Guzzlr, judging from your previous posts you do want a VBA solution. We wouldn't have gotten off 1st base without @jolivanes asking for a view of your data.I know you have used XL2BB in the past so in most cases it would speed things up if you show us from the start what the data looks like.
"not working" also doesn't tell us very much.

From what I am seeing it looks like the data is in US format MDY and from previous posts that MDY is your default system format.
Your reference to not just formatting it as the date makes me think your dates are being recognised as dates and not as text.
If that is the case either of these should work.
The main difference in this text to columns version is that it specifies the date format as xlMDYFormat ( yours had xlDMYFormat - this is where what does "not working" mean comes in)

1) Text to Columns

Sub ExtractDate_TextToColumns()

    With Sheets("Report Paste").Range(Cells(6, "F"), Cells(Rows.Count, "F").End(xlUp))
            .TextToColumns Destination:=.Cells(1, 1), DataType:=xlFixedWidth, _
                FieldInfo:=Array(Array(0, xlMDYFormat), Array(10, xlSkipColumn))
            .NumberFormat = "mm/dd/yyyy"
    End With

End Sub

2) Stripping off the decimals

Sub ExtractDate_Int()

    With Sheets("Report Paste").Range(Cells(6, "F"), Cells(Rows.Count, "F").End(xlUp))
            .Value2 = Application.Round(.Value2, 0)
            .NumberFormat = "mm/dd/yyyy"
    End With

End Sub

Your reference to not just formatting it as the date makes me think your dates are being recognised as dates and not as text.
If that is the case either of these should work.

I believe this is correct. The date/Time stamp is not Text.
I will try the two above and report back.
Thank you

 

[Guzzlr]

2) Stripping off the decimals

Sub ExtractDate_Int()

With Sheets("Report Paste").Range(Cells(6, "F"), Cells(Rows.Count, "F").End(xlUp))
.Value2 = Application.Round(.Value2, 0)
.NumberFormat = "mm/dd/yyyy"
End With

End Sub

This one appears to be working!
The Text to Columns did not work, as it kept giving me a "Subscript out of Range" error.

Thank you for your help!

 

[Alex Blakenburg]

This one appears to be working!
The Text to Columns did not work, as it kept giving me a "Subscript out of Range" error.

I made an error in the code I gave you. To apply it to a range without looping I needed to use a worksheetfunction and the worksheetfunction Int is not available. Instead of Round I should have used RoundDown. Please use this instead.

Sub ExtractDate_Int()

    With Sheets("Report Paste").Range(Cells(6, "F"), Cells(Rows.Count, "F").End(xlUp))
            .Value2 = Application.RoundDown(.Value2, 0)
            .NumberFormat = "mm/dd/yyyy"
    End With

End Sub