Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Aug 2006
    Thanked 0 Times in 0 Posts

    Fix dates with missing years and day (Excel 2003)

    Hi all - I'm having a date conversion issue that I'm hoping to get advice and a fix for:

    After cutting-and-pasting from Word 2000 a bunch of older WP documents into on Excel spreadsheet, I selected Date Format 01/01/2000, and all seemed to format just fine. However, today I realized that all the pasted lines (about 900) are wrong, by four years and 1 day. (4/21/1971 should be 4/22/1975). The correct Date range is from 1974 through 2001.

    This data just needs to be plain Text, and I do not plan to manipulate the text (someday it might be used in an Access table, but that's long off).
    So, my goal is to programmatically change the dates, and then lock them down as Text so they can't get messed with again.

    One of my friends suggested creating a related column and use a simple addition formula, like "[cell]+365*4+1", then use the new data. This looks o.k., but the calculation is one day off most of the time (darn leap years). Also, I want to then save the new column as plain text (only values, not formulas) and fix it as Text with the 01/01/2000 format.

    Is there a better way to fix this? A constant to add to the underlying numbers? Or should I go the route noted above, then cut-and-paste to a text editor, and paste back in as TEXT?

    FYI - I am an old Word VBA geek from way back so VBA code is an option, however, I'm an Excel newbie, so I don't know the best object to use nor the way to loop through a document (DateAdd function looks promising?)

    Anybody have a snippet of code, or a decent way to do this reliably? And of course, I'm under deadline, so the old method of figuring it out on my own isn't really an option today.

    Thanks --- L

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Perth, Western Australia, Australia
    Thanked 0 Times in 0 Posts

    Re: Fix dates with missing years and day (Excel 2003)

    Hi there,

    Try this quick fix.

    Type 1461 into a spare cell (=365*4 + 1)
    Select the cell and copy it.

    Then select your entire list of cells and go to edit>paste special, then select the add operation and press OK.

    The dates should now be changed.

    Then select the list of dates again, copy them and pase tehm back agin to the same place - but use edit>paste special agin , and select the paste values options.

    The dates whould then be text. Now delete the '1461' cell and you should be done.

Posting Permissions

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