Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date format (2007/2003)

    I just D/L some bank transactions in a CSV file and the date came is as M/D/YYYY or MM/DD/YYYY or M/DD/YYYY or MM/D/YYYY as appropriate. However, I need all the dates to be MM/DD/YYYY and I can't seem to come up with a way to do it. Using format cells doesn't seem to help as I suspect the date is really a text. Using DATEVALUE returns an error. Can anyone tell me how to get these dates into MM/DD/YYYY format? SS is attached
    Attached Files Attached Files

  2. #2
    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

    Re: Date format (2007/2003)

    <P ID="edit" class=small>(Edited by sdckapr on 14-Mar-08 17:46. Added PS)</P>Your values are "text" not serialdates:

    Select B1
    Edit -copy
    Select all the data in range A1:A50
    Edit - paste special - add [OK]
    Select A1:A50 and Format Cells as appropriate

    Steve
    PS I don't know why datevalue did not work. It seemed to work for me...
    [I created a formula in B1:
    =datevalue(A1).
    Copy B1 to B2:B50
    select B1:B50
    copy - paste special - value to A1:A50. Delete B1:B50]

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format (2007/2003)

    Thanks for the effort. Nothing works for me. I will have to try it on another computer. When I do datevalue the result is #VALUE!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date format (2007/2003)

    What happens if you open the attached workbook? It uses an alternative approach.
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format (2007/2003)

    What happens if you highlight the data and then run the following macro:


    Sub AppFormat()
    Selection.Value = Selection.Value
    End Sub


    Or you could higlight the data and open the immediate windoe and just run

    Selection.Value = Selection.Value

  6. #6
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format (2007/2003)

    That works well. I even tried the datevalue on your workbook and had the same result. I wonder if I am missing an add-in.

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format (2007/2003)

    what is an immediate window and where would I find it in Excel 2007?

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format (2007/2003)

    I don't know where it is in 2007, but in 2000 it's part of the VBA editor. It is accessed by going to View / Immediate Window or by pressing Ctrl+G.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date format (2007/2003)

    The workbook that I attached is the one you attached to the first post in this thread, with some formulas added. That's all...

Posting Permissions

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