concatenate dates

**JNEAL82** · 10-03-2007, 08:48 AM

Please Help.

In Excel I have my date set up as 1022007. All I want to do is make the date as 10/2/2007. However, when I tryed to use format cell, then date. This didn't work. How do I concatenate the date my results should be mm/dd/yyyy when I tryed to insert function using concatenate it then ask for arguements funtions.??? So lost

**NBVC** · 10-03-2007, 08:58 AM

Try:

=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,IF(LEN(A1)=8,2,1)))

where A1 contain numeric string.

format result as date.

Note this will work with 7 or 8 digit strings...assuming first 2 chars always represent month.

**JNEAL82** · 10-03-2007, 10:02 AM

I don't know if I'm not doing this right but I'm getting a error

where you have A1 in this formula I should click the cell that I want to use right????

**NBVC** · 10-03-2007, 10:06 AM

That formula you would put in a column nearby the original column data and you would replace all the occurances of A1 in the formula with the cell reference of the first cell of your original data. Then you could copy the formula down to convert any subsequent dates....

You can then copy the new data go to Edit|Paste Special...select Values and copy over the original data. Then you could delete the formula data...

**JNEAL82** · 10-03-2007, 10:28 AM

Ok (you are a lifesaver

)

Now I tryed the formula everything worked out fine but some of the days have 10222007 & 1022007 when it got to the double digit days it didn't work??? Any suggestions???

**NBVC** · 10-03-2007, 10:47 AM

are you sure?

I designed the formula to work with 7 digit codes and 8 digit codes... so if there is a single-digit day or double-digit day, it should work.

Do you get an error? or do you get wrong result?

**JNEAL82** · 10-03-2007, 11:04 AM

This is an example of the problem that I'm getting, I've noticed that some of the number are 1022007-11152005-2022007. I'm guessing that the problem is some of the numbers have a Feb 02, 2007 and some have Feb 2, 2007????

Data Formula
1022007 10/2/2007
1022007 10/2/2007
1032007 10/3/2007
1122006 11/2/2006
2012007 8/1/2008
2022007 8/2/2008
2022007 8/2/2008
2022007 8/2/2008
2022007 8/2/2008
2032007 8/3/2008
2032007 8/3/2008

**NBVC** · 10-03-2007, 11:10 AM

Maybe I misinterpreted which digits in your code represent day and which represent month....

Try this revised formula:

=DATE(RIGHT(A1,4),MID(A1,3,IF(LEN(A1)=8,2,1)),LEFT(A1,2))

Does that work for you?

If not, please indicate which digits represent what parts of the date.