working with dates re-visited

rustycar

New Member
Joined
Feb 15, 2002
Messages
10
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.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

rustycar

New Member
Joined
Feb 15, 2002
Messages
10

ADVERTISEMENT

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

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
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

New Member
Joined
Feb 15, 2002
Messages
10
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 :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,870
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top