Excel - removing year from date values 1

[DanCCC]

Hey all,

I want to publish a birthday list with all the dates in order from Jan 1 to Dec 31.

My database contains entire birthdates including the year. So it sorts from oldest people to youngest.

How do I extract just the day and month info (or to put it another way, discard the year info) to get my list to sort correctly? I already know how to use the Format command to not *show* the year.

Thanks,

Dan

 

[dan1967]

Create a new column and enter the following formula in the first cell and drag down your entire column:

=TEXT(A1,"mm/dd")

Cell A1 should oviously be replaced with the actual cell that contains your complete date. You should now be able to sort by the actual mon/day.

Dan.

 

[AnotherHiggins]

Note: You'll need to cut/paste special/values before you can sort on month.

John

Every generalization is false, including this one.
-Unknown

 

[DanCCC]

Dan,

Worked like a charm. Probably lots of people knew the answer to this one, but you were the first.

Thanks!

dan

 

[SkipVought]

Hi,

All you need to do is format the Date column WITHOUT the year. You do NOT need another column, necessarily.

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[AnotherHiggins]

Skip,

If you just change formatting, the year will still determine how the column sorts, or am I missing something?

John

Every generalization is false, including this one.
-Unknown

 

[SkipVought]

John,

You're absolutely correct. I missed the sort part.

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[SkipVought]

Interestingly enough, using the full date, you can do a simple pivot table and group by Month & Day a get the desired result.

I prefer to do as much as I can WITHOUT spreadsheet formulas if I can use a reporting tool instead.

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[dan1967]

Glad I could help.

Dan.