Convert date automatically to “mmm” format

[David_8055]

Hi all,
Can I know how to get month in “mmm” format if I have a date in its previous column.
For eg: column A contains date in short format.
I want column B to show only the month in “mmm” format based on the date in column A. Similarly column C will show only the year in “yyyy” format.
How to do this via VBA? Any help pls

 

[Georgiboy]

Format(Date, "mmm")

Format(Date, "yyyy")

 

[David_8055]

Format(Date, "mmm")

Format(Date, "yyyy")

Thanks got the reply. But that wont work in this scenario.
My column A is 1/4/2022
How can Column B look at this value and enter only the month as “mmm”?
Similarly Column C look at column A and show only the year as “yyyy”

 

[Georgiboy]

It depends on how you want it to happen:

Do you want B & C to auto complete when a date is entered in column A?
Does it have to be VBA or can it be formula?

If formula then:
B formula:

=TEXT(A1,"MMM")

C formula:

=TEXT(A1,"YYYY")

 

[David_8055]

It depends on how you want it to happen:

Do you want B & C to auto complete when a date is entered in column A?
Does it have to be VBA or can it be formula?

If formula then:
B formula:

=TEXT(A1,"MMM")

C formula:

=TEXT(A1,"YYYY")

It gotta be vba as it runs along with my other code. Yes autocomplete

 

[Georgiboy]

I see, is your other code a worksheet change event?
Does your other code put the date in column A?

I am trying to figure out what the trigger would be for the code to run, would it be the date being entered in column A?

 

[David_8055]

I see, is your other code a worksheet change event?
Does your other code put the date in column A?

I am trying to figure out what the trigger would be for the code to run, would it be the date being entered in column A?

Ok, basically my file is a monthly report kind of data.
I use my code to extract certain columns automatically to sheet2 every time I open my excel. Lets say if new data is being added into sheet1, upon running my code desired values will be automatically uploaded in sheet2.
Now this data contains date in 1 column. I would like to extract month and year separately to 2 new columns namely under month and year. This should be updated automatically when I run the code.

 

[bebo021999]

Try:

Sub test()
Dim lr&, i&, rng, arr()
lr = Cells(Rows.Count, "A").End(xlUp).Row
rng = Range("A1:A" & lr).Value
ReDim arr(1 To lr, 1 To 2)
On Error Resume Next
For i = 1 To lr
    If IsDate(rng(i, 1)) And rng(i, 1) <> "" Then
        arr(i, 1) = Format(rng(i, 1), "mmm")
        arr(i, 2) = Format(rng(i, 1), "yyyy")
    End If
Next
Range("B1").Resize(UBound(arr), 2).Value = arr
End Sub