VBA - How do I convert Date into String

Question

I have a date column in EXCEL with the date in the format - 1/27/2019. I want to write a script in VBA to extract just the year portion (2019) and put it in another column.

Do I convert the Date into String first? And then split the String using

`Split(date_as_string, "/")

to get the year portion??

How can I convert the Date into String to achieve this? Any other approach to solve this problem is also appreciated.

skkakkar · Accepted Answer · 2019-01-27 16:36:04Z

2

If your date column is in true date format then Excel YEAR Function can be used to return value of the year in another column. Date reflected in column in this case is in my system local date format. It may be different in your system but YEAR will work as per your date system.

EDIT: If you want to go for VBA then example of macro is:

Sub Year_Only()
    Dim yr As String
    yr = Year(Range("A1"))
    Range("B1").Value = yr
End Sub

edited Jan 27, 2019 at 16:36

answered Jan 27, 2019 at 16:13

skkakkar

2,8482 gold badges20 silver badges32 bronze badges

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

Comments

Gary's Student · Accepted Answer · 2019-01-27 15:51:34Z

2

Something like

Sub JustYear()
    Dim yr As Long
    yr = Split(Range("A1").Text, "/")(2)
    Range("B1").Value = yr
End Sub

answered Jan 27, 2019 at 15:51

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Comments

user10970498 · Accepted Answer · 2019-01-27 15:53:25Z

1

For text representing the year,

range("A1") = format(cdate(date_as_string), "yyyy")

To get a true number representing the year,

range("A1") = val(format(cdate(date_as_string), "yyyy"))

answered Jan 27, 2019 at 15:53

user10970498

Comments

user2261597 · Accepted Answer · 2019-01-27 16:27:31Z

1

Just use the Year() function:

Option Explicit

Sub test()
    Dim yea As Integer
    Dim r As Range

    Set r = Range("a1")
    yea = year(r.Value)
    Debug.Print yea
End Sub

answered Jan 27, 2019 at 16:27

user2261597

Collectives™ on Stack Overflow

VBA - How do I convert Date into String

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related