VBA not changing all dates from text format to date format dd/mm/yyyy

Question

I am having a problem for a long time now.. I have been trying to find some differences between dates and print the result in minutes. However, my problem is that even if I have added a line of code to convert the cell format to date format dd/mm/yyyy, it doesnt apply for the cells, but only for a part of them. Also, for some, it applies, but it creates the date format mm/dd/yyyy. I really dont what to do. These dates are vlookups from another sheet. So, I even wrote a line of code to transform them in their original sheets too. So , it total, just to be sure, I am making two convertions. My code is given below! please help me :)

Sheets("TMS").Select
lastrow = Range("B" & Rows.Count).End(xlUp).Row
With Range("K2:K" & lastrow)
    If Not IsEmpty(Range("K2:K" & lastrow)) Then
        .NumberFormat = "mm/dd/yyyy hh:mm"
    End If
End With
Sheets("TheTracker").Select
lastrow = Range("B" & Rows.Count).End(xlUp).Row
With Range("AO2:AO" & lastrow)
    .Formula = "=VLOOKUP(B2,TMS!B:K,10,FALSE)"
    .NumberFormat = "mm/dd/yyyy hh:mm"
    .Value = .Value
End With

I need it to have this format.. I need the hours and the minutes too — Pericles Faliagas
– Pericles Faliagas, Commented Aug 17, 2017 at 9:02
Please, note you say you want dd/mm/yyyy but your code has .NumberFormat = "mm/dd/yyyy hh:mm". Why doesn't it contains .NumberFormat = "dd/mm/yyyy hh:mm"? — LS_ᴅᴇᴠ
– LS_ᴅᴇᴠ, Commented Aug 17, 2017 at 12:29

Vityata · Accepted Answer · 2017-08-17 08:45:48Z

1

Try to refer better to the Ranges, without using Select, refering to the worksheet that you need, and disregarding the hh:mm. Something like this may work probably:

Public Sub Test()

    Dim lastRow As Long

    With Worksheets("TMS")
        lastRow = .Range("B" & .rows.Count).End(xlUp).Row
        .Range("K2:K" & lastRow).NumberFormat = "mm/dd/yyyy"
    End With

    With Worksheets("TheTracker")
        lastRow = .Range("B" & .rows.Count).End(xlUp).Row
        With .Range("AO2:AO" & lastRow)
            .Formula = "=VLOOKUP(B2,TMS!B:K,10,FALSE)"
            .NumberFormat = "mm/dd/yyyy"
            .value = .value
        End With
    End With
End Sub

answered Aug 17, 2017 at 8:45

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Pericles Faliagas Over a year ago

Unfortunately nothing changed! When I go to the date and check format as general, it still shows the date, not a number, so its still a text

Vityata Over a year ago

@PericlesFaliagas try adding somewhere .Range("K2:K" & lastRow).value = .Range("K2:K" & lastRow).value. Or start recording a macro, until you don't get what you need.

Pericles Faliagas Over a year ago

well still nothing :( half the dates are in date format and half the dates are in text format. Also,half of the dates that are in date format are in mm/dd/yyyy and the other half dd/mm/yyyy. I believe that there is an error with the type of code I am using. Possibly there will be a code instead of .NumberFormat that could do the job..

Vityata Over a year ago

Try rewriting the dates like this 11-Jan-2017 in Excel. Then run it again.

Pericles Faliagas Over a year ago

well the problem is that the value (the date) that I vlookup in the "TMS" sheet is coming from a platform.. So , I need to work with this format as it needs to be automated.

|

teylyn · Accepted Answer · 2017-08-17 09:31:54Z

If a date format is applied to only a few of the cells, it looks like a disjoint of your computer's regional settings and the date format.

If you computer is set to show dates as DMY, then a date like

1/1/2017 will be a real date - January 1
12/7/2017 will be a real date - July 12
13/7/2017 will be a real date - July 13

But

7/13/2017 will not be a date, because the order of day and month are wrong.

The same goes for computers with regional settings to show dates as MDY, popular in the US.

1/1/2017 will be a real date - January 1
12/7/2017 will be a real date - December 7
13/7/2017 will not be a real date - because there is no 13th month.

So, check what format the source data is displayed in: DMY or MDY?

Then adjust your VBA routine to pull the month and the day according to your computer's regional settings.

Moosli · Accepted Answer · 2017-08-17 09:29:49Z

0

If you have the Dates as Text and Excel will not look at them as a date, you can try something like this.

Columns("K:K").Select
        Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 4), TrailingMinusNumbers:=True
            ActiveSheet.Range("K2").Select

answered Aug 17, 2017 at 9:29

Moosli

3,2853 gold badges22 silver badges48 bronze badges

Comments

Ron Rosenfeld · Accepted Answer · 2017-08-17 10:02:54Z

0

I will guess that the data came from a CSV file that you OPEN'd.

If that is the case, you are better off doing an IMPORT (usually on the DATA ribbon).

When you do this, the Text Import Wizard will open and you will be able to specify the date format of the incoming data.

Excel will then convert all of these dates to real dates, and you will be able to apply the number format. This IMPORT process can be automated through VBA if necessary.

answered Aug 17, 2017 at 10:02

Ron Rosenfeld

62.1k7 gold badges35 silver badges71 bronze badges

Collectives™ on Stack Overflow

VBA not changing all dates from text format to date format dd/mm/yyyy

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related