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

 

[jolivanes]

Give us a few examples of what the data to be changed looks like.
How are the dates entered in these cells? Manually, macro, formula?

 

[Guzzlr]

Give us a few examples of what the data to be changed looks like.
How are the dates entered in these cells? Manually, macro, formula?

Data is an export from enterprise software.
Example: beginning in Cell F6, F5 is a header which does not change. I am trying to strip the time stamp away and just have the date. Not just format it different, but completely take the time stamp out so just the date is showing.
Thanks for the help

 

[Burrgogi]

Data is an export from enterprise software.
Example: beginning in Cell F6, F5 is a header which does not change. I am trying to strip the time stamp away and just have the date. Not just format it different, but completely take the time stamp out so just the date is showing.
Thanks for the help

Does it have to be a VBA solution? Seems like you could easily solve this situation by using the Text To Columns feature.

1) Select Col. F. (entire column)
2) Click on the Data tab in the ribbon.
3) Click on Text To Columns
4) Select Delimited option (should already be checked by default).
5) In the Delimiters type, UNSELECT the tab option and select the Space option instead.

Screenshot as an example:

It will separate the date & time in to its own columns. From there it would be a simple matter of deleting the undesired columns.

 

[Burrgogi]

There is an alternate solution to this using the following formula.

=LEFT(F6, FIND(" ", F6)-1)

This will only work if it's plain text. Considering that the data comes from enterprise software, there's a likelihood it's formatted as something else entirely (e.g. custom date/time format).

I can't tell from your screenshot how column F is formatted. If the above formula does not work, you'll have to provide more details.

Select one of the cells in question (under col. F) and Right-click on it. Then click on 'Format Cells...." in the context menu that pops up. Then tell us what you see under the Numbers tab. This will tell you the format type.

 

[MARK858]

If it is a real date and time you could just use the formula

=INT(F6)

dragged down, formatting the cells as a date format

 

[Burrgogi]

If it is a real date and time you could just use the formula

=INT(F6)

dragged down, formatting the cells as a date format

I'm not the OP, but I appreciate that answer. You learn something new everyday here! Much easier & simpler than my formula!

 

[Alex Blakenburg]

@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

 

[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

If I use XL2BB. Exactly what part of the report do you want?

 

[Alex Blakenburg]

See if any of the suggestions provided work for you for now.