working with dates re-visited

[rustycar]

I have several thousand cells of numbers, each representing a percentage of shellfish weight taken on a specific date. The data set spreads from 1999-2004.

I am plotting a time scale graph of these data points however when plotted, the graph is extremely wide - seeing the x-axis goes from Jan, 1999- through 2004 and I must go in 7 days increments.

I can set the x-axis to cover jan- Dec and have it YEAR neutral and plot all the data that way....the problem is that for the few thousand cells of data, I must change all the dates to a common one. My questions are:

How can I change the YEAR referenced in all these cells to a common one, in one or two steps, rather than having to change it manually one by one?

Is there a better way to plot this?
thanks so much.

 

[staticbob]

Hey Rusty,

Can you post an example of your data ?

Bob

 

[rustycar]

how do I attach a file?

thanks

 

[staticbob]

Use the hTML Maker available on this site, its a plug-in that will give you an extra menu in Excel. You then just select the area you want to post, convert it to HTML then copy and paste it into your post.

http://www.interq.or.jp/sun/puremis/colo/HtmlMaker.htm

Hope this helps !
Bob

 

[rustycar]

here's small example:

19-Dec-00
26-Dec-00
14-Jan-01
05-Mar-01
16-Nov-01
12-Feb-02
15-jul-02
23-Sep-02
14-Jan-03

How can I change the YEAR in these examples to say, 2000 (e.g., 14-Jan-00), without changing the month or day? More specificly, can it be done for ALL cells in a single step without hanving to change each year manually, one by one.....? Remember I have >1,800 dates covering 5 years.

hope this clears up my question.....its a bit bizzare.

thanks

 

[staticbob]

Firstly, format all the dates so they show with the full year. eg, 2002 not just 02. You can do this in format cells.

Then its just a matter of doing a few find and replace. Find 2001 and replace with 2000 . . . find 2002 and replace with 2000

This is my way of thinking anyway, Im sure there will be a quicker way, and perhaps also a more useful way of plotting your current data without changing it !

Bob

 

[rustycar]

hey it works!....you are a whiz.....thanks for your help.


I can now plot the graph using a scatter plot....the x-axis will only cover 1 year but in the title I will put 1999-2003....each data point will land on its correspnding date...for the purpose of this graph, it is not important for me to indicate whether it as in 1999 or 2003...just the specific day and month.

thanks again

rustycar