VBA Bug TextToColumns in Date

[rodrigo_m_almeida]

Good Morning,

I'm trying to extract only the days from this date string

This code

        Range("Query01[Data]").Select
        Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 2), Array(2, 9), Array(3, 9)), _
        TrailingMinusNumbers:=True

It's just that I'm not getting it, he keeps repeating the dates

Does anyone have any ideas ?

 

[Joe4]

If it is a valid date entry (and not text), there really aren't slashes in the date - that is just in the format applied to the cell.
Dates in Excel are stored as numbers, specifically the number of days since 1/0/1900 (to see this, just change the format of the cell to General).

To get the Day value from a date, just use the DAY function, i.e.
=DAY(A2)

 

[rodrigo_m_almeida]

If it is a valid date entry (and not text), there really aren't slashes in the date - that is just in the format applied to the cell.
Dates in Excel are stored as numbers, specifically the number of days since 1/0/1900 (to see this, just change the format of the cell to General).

To get the Day value from a date, just use the DAY function, i.e.
=DAY(A2)

How can I use this function in vba to get the days of all cells in that column ?

 

[Joe4]

The DAY function works in VBA too.

What do you mean to get the days of all cells in that column?
Where do you want this value to show?
In the next column over (column B), or somewhere else?

 

[rodrigo_m_almeida]

The DAY function works in VBA too.

What do you mean to get the days of all cells in that column?
Where do you want this value to show?
In the next column over (column B), or somewhere else?

Extract days and show in the respective cell

 

[Joe4]

So, you want to overwrite the date with the day?

This VBA code should do that:

Sub MyReplaceCode()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows starting on row 2
    For r = 2 To lr
'       Replace date entry in column A with the Day
        Cells(r, "A") = Day(Cells(r, "A"))
    Next r
    
'   Change the format of column A to General
    Range("A:A").NumberFormat = "General"
        
    Application.ScreenUpdating = True
   
End Sub

 

[rodrigo_m_almeida]

So, you want to overwrite the date with the day?

Yes, Thanks

 

[Rick Rothstein]

If Joe4 is correct in what you want, that can be done without using a loop as well...

Sub ReplaceDatesWithDayNumbers()
  With Range("A2", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate("DAY(" & .Address & ")")
    .NumberFormat = "General"
  End With
End Sub

 

[Joe4]

You are welcome.
Glad we were able to help!