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

 

[VoG]

Or maybe

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 = Int(.Value)
    End With
Next i
End Sub

 

[Jameo]

Any idea why your code would be running so slowly VoG? Its taking an age

 

[VoG]

How many rows? Are there formulas that reference the dates?

 

[Jameo]

There are currently 31 rows, but yes there is several vlookups that reference the dates, that must be the reason, it must be recalculating the cells after each date is updated. I'll try populating the dates before filling in the vlookups.

Cheers

 

[VoG]

Try

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

 

[Jameo]

Thanks for all the help both of you, finally working correctly now

Edit: Didn't see your latest post until now VoG, out of curiosity, what does that last line do? Stall the calculations?

 

[SLF_code]

I would use

If IsDate(testDate) = True Then
testDate = Format(testDate, "dd/mm/yyy")
testDate = CDate(testDate)
End If

As I feel it is more elegant