Speed up code: convert datetime to just date

[always_confused]

Hello, I am trying to convert about 75 000 lines of datetime formate data (YYYY/MM/DD HH:MM:SS) into just dates (YYYY/MM/DD). The only way I have found to do this it to concatenate the YEAR(), MONTH(), DAY() parts of the data in one column into another column. However, with so many rows, this takes FOREVER and makes Excel stop responding. There must be a better way to do this, but I just don't know what it is. I need to do this only for date on the row where another value is <> 0. Here's my code:

Dim lastrow As Long
lastrow = Sheets("lot").Range("A" & Rows.count).End(xlUp).Row 

For i = lastrow
If Sheets("sheet1").Range("A" & i ).value <> 0 Then
Sheets("sheet2").Range("A" & Rows.count).End(xlUp).Offset(1, 0).value = Year(Sheets("sheet1").Range("B" & i).value) & "/" & Month(Sheets("sheet1").Range("B" & i).value) & "/" & Day(Sheets("sheet1").Range("B" & i).value)
End If
Next i

 

[dmt32]

You should be able to use DateValue Function to do what you want as this will ignore the Time part of the date.

Untested but try following & see if helps

Sub ChangeDate()
    Dim lastrow     As Long, i As Long
   
    Application.Calculation = xlCalculationManual
    With Worksheets("lot")
        lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
       
        For i = 1 To lastrow
            With .Cells(i, 1)
                If IsDate(.Value) Then
                    .Value = DateValue(.Value)
                    .NumberFormat = "YYYY/MM/DD"
                End If
            End With
        Next i
    End With
    Application.Calculation = xlCalculationAutomatic
End Sub

Adjust code to meet project need as required

Dave

 

[always_confused]

Hello, I am trying to convert about 75 000 lines of datetime formate data (YYYY/MM/DD HH:MM:SS) into just dates (YYYY/MM/DD). The only way I have found to do this it to concatenate the YEAR(), MONTH(), DAY() parts of the data in one column into another column. However, with so many rows, this takes FOREVER and makes Excel stop responding. There must be a better way to do this, but I just don't know what it is. I need to do this only for date on the row where another value is <> 0. Here's my code:

Dim lastrow As Long
lastrow = Sheets("lot").Range("A" & Rows.count).End(xlUp).Row

For i = lastrow
If Sheets("sheet1").Range("A" & i ).value <> 0 Then
Sheets("sheet2").Range("A" & Rows.count).End(xlUp).Offset(1, 0).value = Year(Sheets("sheet1").Range("B" & i).value) & "/" & Month(Sheets("sheet1").Range("B" & i).value) & "/" & Day(Sheets("sheet1").Range("B" & i).value)
End If
Next i

TYPO : Here's the correct code

Dim lastrow As Long
lastrow = Sheets("sheet1").Range("A" & Rows.count).End(xlUp).Row

For i = lastrow
If Sheets("sheet1").Range("A" & i ).value <> 0 Then
Sheets("sheet2").Range("A" & Rows.count).End(xlUp).Offset(1, 0).value = Year(Sheets("sheet1").Range("B" & i).value) & "/" & Month(Sheets("sheet1").Range("B" & i).value) & "/" & Day(Sheets("sheet1").Range("B" & i).value)
End If
Next i

 

[always_confused]

You should be able to use DateValue Function to do what you want as this will ignore the Time part of the date.

Untested but try following & see if helps

Sub ChangeDate()
    Dim lastrow     As Long, i As Long
 
    Application.Calculation = xlCalculationManual
    With Worksheets("lot")
        lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
     
        For i = 1 To lastrow
            With .Cells(i, 1)
                If IsDate(.Value) Then
                    .Value = DateValue(.Value)
                    .NumberFormat = "YYYY/MM/DD"
                End If
            End With
        Next i
    End With
    Application.Calculation = xlCalculationAutomatic
End Sub

Adjust code to meet project need as required

Dave

I get a type mismatch at the

 .value = DateValue(.value)

do I need to format the calls as date first maybe?

 

[dmt32]

I get a type mismatch at the

 .value = DateValue(.value)

do I need to format the calls as date first maybe?

this line

If IsDate(.Value) Then

should determine if cell contains a date & if so, DateValue should work without erroring
but to understand what is going on, It would be helpful if you a sample of your worksheet using MrExcel Addin XL2BB - Excel Range to BBCode

Dave

 

[always_confused]

this line

If IsDate(.Value) Then

should determine if cell contains a date & if so, DateValue should work without erroring
but to understand what is going on, It would be helpful if you a sample of your worksheet using MrExcel Addin XL2BB - Excel Range to BBCode

Dave

Fixed it actually. It's working great now. Thanks, Dave

 

[dmt32]

Fixed it actually. It's working great now. Thanks, Dave

glad you resolved it
Appreciate feedback

Dave