Results 1 to 4 of 4

Thread: Date format

  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date format

    A database was sent to me with the dates 01/02/48 -with two spaces before the 01 for some reason.
    I would like it to be 01/02/1948. Right click format does not work. Here is a copy of the database dates
    I'm using excel 2003
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,830
    Thanks
    136
    Thanked 483 Times in 460 Posts
    In your sample file, it is THREE spaces not TWO before the date value.
    You could convert by using a formulaa in column [B} e.g.
    =DATE(RIGHT(A2,2),MID(A2,4,2),MID(A2,7,2))

    ..and copying down

    zeddy

  3. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    Looks like your "dates" are actually text strings even though the cell format is "date" so you'll have to convert them to a date using Datevalue()

    You should also watch out for Excel's default for deciding a 2 digit year is in the 20th or 21st centuries.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can find all spaces and replace with nothing and excel will convert them to dates. You will have to adjust some as mentioned by iansavell, since the digit year "20" is presumed to be 2020 and not 1920. (00-29 are presumed to be 2000-2029 and 30-99 will be 1930-1999).

    Steve

Posting Permissions

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