Formatting Dates

G

Guest

Guest
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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
Back
Top