1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2000 date merge

Discussion in 'Business Applications' started by TRG Fred, Oct 25, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. TRG Fred

    TRG Fred Thread Starter

    Joined:
    Oct 25, 2007
    Messages:
    6
    I'm using Excel 2000 and XP. I'm doing form letters and trying to merge dates that are formatted "20020721". How can I change the format to 7/21/2002 when merged?
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Are the dates entered like 20020721 or is that a format? What do you see in the formula bar at the top of the spreadsheet when you click on one of the dates?
    Knowing this will affect knowing what has to be done next.
     
  3. lika2know

    lika2know

    Joined:
    Oct 10, 2007
    Messages:
    33
    If the dates are stored as YYYYMMDD, you'll want to convert them to Office date math; then you can control the format using format-cell. I'd do it by using the function DATEVALUE after stripping the source into the three parts and then putting them back together again (using the text functions of left, mid, right, and concatenate with "/"). You'll get a value 37458 for 7/21/02...
    Lika2know
     
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    That's what I was getting at - Excel doesn't see 20020721 as a date - rather, it treats it as a number. But if it is just a rather unusual format, then changing it is almost no work.
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    If you need to parse text, check out the LEFT(), MID(), and RIGHT() functions. You're always better off keeping dates stored as dates, not text. You can format any way you'd like. You will know the date value as slurpee states, looking in the formula bar. Dates are really different and stand alone compared to the other values Excel handles.
     
  6. TRG Fred

    TRG Fred Thread Starter

    Joined:
    Oct 25, 2007
    Messages:
    6
    The data is not formated as a date. All of the numbers are shown as yyyymmdd but show up as a number in the formula bar. When I manually input a / between years, month and day excel recognizes it as a date and shows it as mm/dd/yyyy but I don't want to manually change every date. Any "easy" fixes?
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Such a column doesn't change to mm/dd/yyyy for me, so I can't figure out what you have.
    I mean, I can put two "/" in to break it up, but what you have then is sometext/somemoretext/stillmoretext, not a date, despite what it looks like. You can't use it for calculations - say, length of time between this date and that one. Maybe firefytr can come up with something....
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I'd check it with a date-brute-force-type-text-conversion format (long-winded, I know). Let me explain...

    Code:
    =IF(ISERR(TEXT(A1,"mm/dd/yyyy")),DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),A1)
    Try it in an adjacent column. This assumes your data is housed in column A starting with A1 and goes down. So, enter this formula in B1 and copy down to the end of your data set. Select the column, press Ctrl + C to copy, then press Alt + E, S, V, Enter to paste special as values.

    Hope I understood you.

    HTH
     
  9. TRG Fred

    TRG Fred Thread Starter

    Joined:
    Oct 25, 2007
    Messages:
    6
    Thank You! Thank You! Thank You! The formula works for me. I appreciate your time and effort in responding to me.
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    You're very welcome. :)

    Don't forget to mark your thread as Solved by going to Thread Tools | Mark Solved | Perform Action.
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/643472

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice