Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Formatting Dates

  1. #1
    Guest

    Default

    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    Guest

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Guest

    Default

    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.

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Guest

    Default

    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.

  10. #10
    Board Regular RichardS's Avatar
    Join Date
    Feb 2002
    Location
    Victoria, Australia
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •