Formatting Dates

G'day all.

I was wondering if there is a way to adjust the way the following dates appear. For some reason the date format changes after every 15 or so entries.

11/27/2000
11/28/2000
11/29/2000
11/30/2000
1/12/00
4/12/00
5/12/00
6/12/00
etc...

It continues to change like this. Apart form manually adjusting (it goes back to the late 1970's!) is there an easier way to put it all in dd/mm/yyyy.

Thank you for your help

[Paul B]

Select the column and format it as the date format you want. Paul B

Thanks mate, but i have tried that and the following results are produced:

11/27/2000
11/28/2000
11/29/2000
11/30/2000
12-Jan-00
12-Apr-00
12-May-00
12-Jun-00
12-Jul-00
12-Aug-00
12-Nov-00
12-Dec-00
12/13/2000
12/14/2000
12/15/2000
12/18/2000
12/19/2000
12/20/2000
12/21/2000
12/22/2000
12/25/2000

or;

01/20/1984
01/23/1984
01/24/1984
01/25/1984
01/26/1984
01/27/1984
01/30/1984
01/31/1984
1/2/1984
2/2/1984
3/2/1984
6/2/1984
7/2/1984
8/2/1984
9/2/1984
10/2/1984

It seems that sometimes it is the format dd/mm/yy and other times mm/dd/yy. So when I try to change all it throws it all over the place.

I could go through and then rechange those that dont display as i want but this would take a long time.

I hope this makes sense.

Thanks for your help

[Paul B]

Are some of the cells formated as text
I came up with the same thing when some were formated as text, if your dates are in column A, in column B put =text(A1), if it is text it will say true, if this is the case I think you will have to reenter the dates for the formatting to take.

[ This Message was edited by: Paul B on 2002-02-18 18:44 ]

No they are all date. I was just changed the date to '14-Mar-98' to give an indication of what is occuring.

It really looks like the date format swaps from mm/dd/yyyy to dd/mm/yy. I need to be able to, without going through each, make all the one format.

eg. It currently interprets a part of the data on some as 'mm' and others as 'dd' so doing a column format produces those results shown.

Thanks mate.

[Derek]

If they are not produced by formulas, you could try:
Format a column in another sheet the way you want it, then copy your originals and post them as values into the other sheet. Then copy this column and do a normal paste back over the top of your originals.
Hope this helps
Derek

[Barrie Davidson]

Do you know for sure what the format is (mm/dd/yy or dd/mm/yy)? Looks to me like they are formatted like mm/dd/yy. If that is the case, why not try Data|TextToColumns (from the main menu). When you get to Step3, select Date as the column format and specify MDY.

[Derek]

I have just remembered something that happened to me. I used vb code to change entries to Upper case on a worksheet. Cells that contained only a date were reversed.
Only a thought
Derek

Thanks guys.

Your suggestion works but only on part of the data. When the date format changes as it does every 15 or some points I would need to change the dmy - mdy.

Thanks again for your help. Looks like a bit of time may need to be spent manually changing.

[RichardS]

I had a problem like this at one stage. You will notice I'm in Australia. Our date format is dd/mm/yyyy. However most computers come preloaded with an American date format, mm/dd/yyyy. I had users entering dates in the Australian format. If the day was bewteen 1 and 11, it would store the data as a date, with the day as the month and month as the day, otherwise it would assume it was text, no matter the format. Check your regional settings.
Richard