CODEWITHSUNDEEP
excel
nnnn7993
nnnn7993

Reputation: 23

excel replace values of multiple rows

I have an excel-question that i so-far haven't found a solution to.

Say i have for instance two columns date and name

the data could be something like

date | name
02-05-1996 | smith
02-06-1996 | johnson
07-09-1996 | griffin
02-08-2001 | carlsen
17-11-2001 | petersen

and i want to change the values in the date-column so it is only the year that is left - like this:

date | name
1996 | smith
1996 | johnson
1996 | griffin
2001 | carlsen
2001 | petersen

how could you do this?

Thank you very much for your help!

Upvotes: 2

Views: 2556

Answers (3)

shahkalpesh
shahkalpesh

Reputation: 33474

option explicit
Sub ChangeDateValuesToYear(ByVal startCell As Range)
Dim cell

Dim lastCellInThisColumn As Range
Set lastCellInThisColumn = startCell.End(xlDown)

Dim dateDataRange As Range
Set dateDataRange = Range(startCell, lastCellInThisColumn)

For Each cell In dateDataRange
    cell.Value = Year(cell.Value)
Next

dateDataRange.NumberFormat = 0
End Sub

EDIT: Assuming the cell for dates is in column A, starting from A1, you can do the following

call ChangeDateValuesToYear(Range("A1"))

Upvotes: 0

stenci
stenci

Reputation: 8481

Assuming that the dates start from A1, select C1 and type =Year(A1) and copy it down to fill the full range.

If you want to get rid of the old dates, select the range just created with the years, cut it, select the original range with the full dates, paste only values.

Upvotes: 0

ashareef
ashareef

Reputation: 1846

What you are looking for is the YEAR() function

http://office.microsoft.com/en-ca/excel-help/year-HP005209343.aspx?CTT=5&origin=HP005204211

Assuming your date column is A

= year(A1)

Upvotes: 1

Related Questions