VBA - Modify Cell Date to remove Time Value

[Jameo]

Hi guys,

I have some dates in the format:

dd/mm/yy hh:mm

with some other dates in the format:

dd/mm/yy.

The ones in the format dd/mm/yy hh:mm are severly messing up my vlookups (despite leaving the last argument absent, or as false). Ideally, when the data is imported I would like my VBA to remove the time stamp completely, leaving the date just as dd/mm/yy.

Does anyone know how to accomplish this?

Thanks all

 

[RoryA]

Absent is not the same as FALSE in VLOOKUP, by the way.

If they are actual date values, then using Int on them will remove the time part.

 

[Jameo]

Yeah I know Rory, I just couldn't remember which did what, I know that one should effectively ignore the hh:mm bit. I tried them both, and neither got my vlookups working.

How would I use int? Never used it in VBA before.

Thanks for the reply

 

[VoG]

Try like this

Sub ToDate()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        .NumberFormat = "dd/mm/yy"
        .Value = CLng(.Value)
    End With
Next i
End Sub

 

[Jameo]

Thanks Peter,

I am getting a type mismatch on .Value = CLng(.Value)

int works, just not sure how to incorporate it into my VBA code

 

[VoG]

Are they true dates or "text dates".

The code worked for me.

 

[Jameo]

The cells are formated as dd/mm/yyyy, but the actual cell value for example would be:

01/01/2011 07:00

 

[VoG]

Make sure here aren't any headings in the range, for example

Sub ToDate()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("A" & i)
        .NumberFormat = "dd/mm/yy"
        .Value = CLng(.Value)
    End With
Next i
End Sub

 

[RoryA]

If the display doesn't match the formatting, then it's text, so you need DateValue:

.Value = DateValue(.Value)

 

[Jameo]

Appologies, it was because my first row of data was a title not a date.

The above seems to work well, all be it very slowly. Not sure why, maybe is just my computer.

Thanks again