Date/year problem

[Tim_J]

Hi,

There's probably a simple answer to this question...

When I type in the date in a cell without the year, eg. 20/11, it automatically adds /2006 in the formula bar,

Is there a way I can stop it doing this, as I want to have columns with different years?

Thanks

[ResulG]

Hi,

just highlight the cells or the column and right click. Select Format Cells. Under category select date and under type, select the desired format you want it in.

ResulG

[mudraker]

No unless you change the cell format to text or you use the ' as the 1st charater entered - this forces Excel to accept the entry as text

The only other way is to add the year as well when you enter the date

[Tim_J]

Thanks, but thats not quite what I am looking for because I want to be able to graph the data. I want column A to have a list of dates in order, i.e. 01/01, 02/01, etc to 31/12. Then column B=2004, column C=2005, column D=2006.
But because Excel thinks the dates in column A are 2006 it will be impossible to have the data in a graph and have 3 lines to represent the 3 years.
Any ideas?
Thanks.

[Tim_J]

Ok, it seems it doesn't actually matter that Excel recognises column A as 2006, I thought it would!
Thanks anyway!

[Bryan Hessey]

Ok, it seems it doesn't actually matter that Excel recognises column A as 2006, I thought it would!
Thanks anyway!

Tim,

If you hit the graph problem of dates (where the graph decides to do a 'daily' on the Axis), you can use a 'Text' date where the
=Text(A1,"dd/mm")
is used as the chart Axis labels.

hth
---

[Tim_J]

Thanks Bryan.

I have another question. How do I make Excel automatically copy data from one worksheet to another? For example, I have my main sheet of data and I also have a summary sheet. How can I make Excel automatically copy the data for a particular date from the data sheet and paste it next to the corresponding date on the summary sheet?

Hope that makes sense.

Thanks

Tim

[Mark@Work]

Check out the Lookup and Reference functions in Excel.
In particular HLookup(), VLookup(), Match().

I would probably use Match() to find the position of the date you want on sheet1, then use Offset() to transfer the data from around that position.

There are other ways of doing this, this is just the way I would probably use. The precise usage will depend on how your data is arranged etc.

Mark.