010407 to 1/4/2007

[QuietRiot]

how could I get date from 010407 to 1/4/2007

VBA or formula would be helpful

i will be using it in code though.

 

[Scott Huish]

If your orignal dates are text, not numbers formatted that way, then perhaps something like this:

Sub Test()
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, FieldInfo:=Array(1, 3)
End Sub

 

[Peter_SSs]

Assuming the 010407 is 6 text characters and not a 5 digit number formatted to show the leading 0, then one formula approach would as shown below, with cell B1 formatted with the appropriate Date format.

Excel Workbook
	A	B
1	010407	1/4/2007
Date

Edit: Or following on from HOTPEPPER's idea, a manual approach..
Data|Text to columns...|Next|Next|Date|Finish
Then format the cell/column to a different date format if required.

Edit again: Overlooked this though, oops.

i will be using it in code though.

 

[pgc01]

Also

=VALUE(TEXT(A1,"00\/00\/00"))

No problem if you don't have the leading "0".

EDIT:
Like Peter I also overlook the code part

DateValue (Format([a1], "00/00/00"))

 

[Jon von der Heyden]

=VALUE(TEXT(A1,"00\/00\/00"))

Pgc01, this is new to me....

Why the \/? I notice that it works brilliantly, but not really sure why?

Regards
Jon

 

[pgc01]

Hi Jon

The "\" inside the format string is an escape charater, it tells excel not to interpret the next character. Since "/" inside the format string has a special meaning, it's a date delimeter, if I would not use the "\/" it would mess up things up.

This way you get the string "01/04/06" out of the text function, that is then converted using the Value() function.

Remark: You could write the "/" between double quotes.

 

[barry houdini]

or try

=TEXT(A1,"00-00-00")+0

 

[Jon von der Heyden]

Hi Jon

The "\" inside the format string is an escape charater, it tells excel not to interpret the next character. Since "/" inside the format string has a special meaning, it's a date delimeter, if I would not use the "\/" it would mess up things up.

This way you get the string "01/04/06" out of the text function, that is then converted using the Value() function.

Remark: You could write the "/" between double quotes.

Very nice! Thanks pgc01, I've added this to my favourites for future reference!

Jon

 

[pgc01]

=TEXT(A1,"00-00-00")+0

Yes, simpler. I didn't know that the "-" would also work with the US regional settings.