Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Date Format (Office 2003)

    Hi

    I have a spreadsheet where someone has entered 856 dates in the format 10.9.77, Is there any way I can convert these to 10/8/77 without having to do them all individualy

    Thanks in advance

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Format (Office 2003)

    If the cells should have the same value then select the 856 cells, type 10/8/77 and press enter.

    If you need to change the cells from text value to a date value then an easy way is to use the free add-in ASAP Utilities. This will add a new menu containing over 300 tools, one of them (ASAP Utilities>Text>Convert dates) will do this.

    There are other ways of doing this using formulas or vba, if you require them then post back.

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

    Re: Date Format (Office 2003)

    Without special utilities: say that the dates are in A1:A856.
    Make sure that B1:B856 is empty; if necessary, insert a blank column.
    In cell B1, enter the following formula: =DATEVALUE(SUBSTITUTE(A1,".","/"))
    Double click the fill grip (the little black square in the lower right corner) of B1 to fill down the formula.
    Copy B1:B856 to the clipbboard, then Edit | Paste Special..., option Values to remove the formulas.
    You can now delete column A.

  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

    Re: Date Format (Office 2003)

    In addition to Hans suggestion, find/replace should also work and needs no formula:
    Select the cells
    Edit - replace
    find: (no quotes): "."
    replaceno quotes): "/"
    <replace all>

    They should turn into dates automatically.

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Date Format (Office 2003)

    Hi Tony

    Their all different dates looks as if ASAP is the way to go.

    Thanks for the help

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Date Format (Office 2003)

    Hi

    Thanks to everyone. plenty of opitons here

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Format (Office 2003)

    This does work.
    Well, it does for me.
    But I'm using office XP. Maybe 2003 is too clever to do the simple tricks?

Posting Permissions

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