Solved: Excel 2000 date merge

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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?
 
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.
 
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
 
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.
 
Joined
Jul 25, 2004
Messages
5,458
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.
 

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?
 
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....
 
Joined
Jul 25, 2004
Messages
5,458
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
 

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.
 
Joined
Jul 25, 2004
Messages
5,458
You're very welcome. :)

Don't forget to mark your thread as Solved by going to Thread Tools | Mark Solved | Perform Action.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top