How can I drop the year from mm/dd/yyyy data?

[Guest]

I have a large data set consisting of values from multiple years that I would
like to analyze by time of year. ie: I want to look at the month/day for each
value while ignoring the year.

Specifically I want to end up with a graph showing time of year (in months)
across the x-axis and showing all my data points as if they were from this
one hypothetical year.

Seems like it should be simple but I can't seem to crack it. Any help is
greatly appreciated.

 

[Mark]

Hi,
Try Format > Cells > Custom:
dd/mm



-Mark

 

[Frank Kabel]

Hi
use
=YEAR(A1)
in a helper column

 

[Guest]

Thanks! This gets me closer but what I actually want to keep is the month
and day.

So is there some way to do a formula like: =MONTH/DAY(A1) ??? -obviously
that formula doesn't work, but you get the idea.

I'm starting out with 1/18/98, 5/4/96, 8/23/99....

And would like to end up with 1/18, 5/4, 8/23....

Just changing the cell format to mm/dd doesn't work because excel still
incorporates the year when you try to make a graph based on those values.

Help? Thanks!

 

[Alex Delamain]

=month(a1)

will give you the month for any given date (ie not year specific)

if you want day and month then try using

=text(a1,"mmdd")

this would turn 23rd September any year into 092

 

[Frank Kabel]

Hi
try
=TEXT(A1,"MM/DD")

 

[Andy B]

Try this
=CONCATENATE(DAY(A1),"/",MONTH(A1))

Andy

 

[Guest]

Thanks! This CONCATENATE method and the =TEXT(A1, "MM/DD") both work. So
3/15/04, 5/21/99.... now read 3/15, 5/21.... just like I wanted.

Now my problem is that I need excel to recognize these new values as dates
again so I can graph them as month/day. I've tried re-formatting the cells to
the appropriate date format, but it won't graph them properly.

Any ideas on how to complete this second step? Thanks again.

 

[Frank Kabel]

Hi
this is not possible
Either you have date values formated according to your specification or
you have a text value.

You may try using the date values and format them within the chart

 

[Debra Dalgleish]

With your first date in cell A2, enter the following formula in cell B2:

=DATE(2004,MONTH(A2),DAY(A2))

Copy down to the last row of data

Create the chart using the dates in column B, and format the dates to
show only the month and day.

 

[Guest]

That's great! THANK YOU ALL!

With your first date in cell A2, enter the following formula in cell B2:

=DATE(2004,MONTH(A2),DAY(A2))

Copy down to the last row of data

Create the chart using the dates in column B, and format the dates to
show only the month and day.