Need Excel macro

[luculente]

I?m hoping someone can help me to write a macro to fix this problem. I have a worksheet that contains a column of dates that were entered as text in this manner: dd mmm yyyy. I need to sort this worksheet based on the year. I want to be able to go into each cell and delete the day and month, leaving just the year so it can then be sorted. There are over 7000 entries so I don?t want to do it by hand. Is there anyway of recording or writing a macro that will do this?

Thanks.

[mikerickson]

If A:A is your column of dates, putting this in B1 and filling down will give you a column of years

=YEAR(DATEVALUE(A1))

[unmarkedhelicopter]

I would suggest NOT doing that !
just reformat your cells to yyyymmdd
Then sort as you wish
If you really want just the year reformat as yyyy

[Bob Phillips]

I?m hoping someone can help me to write a macro to fix this problem. I have a worksheet that contains a column of dates that were entered as text in this manner: dd mmm yyyy. I need to sort this worksheet based on the year. I want to be able to go into each cell and delete the day and month, leaving just the year so it can then be sorted. There are over 7000 entries so I don?t want to do it by hand. Is there anyway of recording or writing a macro that will do this?

Thanks.


If they are real dates, just sorting will sort them such that all dates ijn one year will be grouped.

If the cells are really text you need to add this in a helper column

=RIGHT(A1,8,4)

copy it down and sort by this helper column.

[Norie]

Why not convert them to real dates?

Select the column, goto Data>Text to columns..., select Delimited on the first step and on the last step select date with DMY from the dropdown.

[luculente]

:clap: Thank you so much. This worked great.

[luculente]

:bow:
If they are real dates, just sorting will sort them such that all dates ijn one year will be grouped.

If the cells are really text you need to add this in a helper column

=RIGHT(A1,8,4)

copy it down and sort by this helper column.

[Norie]

Out of interest could you tell us what actually worked?

You were offered 4 different suggestions.

[luculente]

Out of interest could you tell us what actually worked?

You were offered 4 different suggestions.
Yes, sorry. It was this one:

If the cells are really text you need to add this in a helper column

=RIGHT(A1,8,4)

copy it down and sort by this helper column.

Except I used the following: =RIGHT(A1,4) to get all but the year removed. Then I was able to sort by year.

Thank you all for your help.

[Norie]

Thanks for the reply.:)

Though I would suggest that if these values are actually text and they are meant to be dates that you actually convert them to 'real' dates.

Then you'll be able to use them as such in subsequent calculations.

[Bob Phillips]

Yes, sorry. It was this one:

If the cells are really text you need to add this in a helper column

=RIGHT(A1,8,4)

copy it down and sort by this helper column.

Except I used the following: =RIGHT(A1,4) to get all but the year removed. Then I was able to sort by year.

Thank you all for your help.

Sorry about my formula, I must of been in cuckoo land at the time.