HELP- Unable to Manipulate Text

[Lucasv98]

I am attempting to convert the text in column 4 into column 5 in MM/DD/YYYY Format. The first 43 entries show my harcoded work, where as the numerical strings after were produced using the formula =RIGHT(A1,8). I am unable to format the resulting data as a date like those above, and was wondering if anyone had a work around. Thanks

 

[Chihiro]

Use Date() function.
Ex:
=DATE(RIGHT(A1,4),LEFT(RIGHT(A1,8),2),LEFT(RIGHT(A1,6),2))

Edit: Alternately use DateValue & Replace.
=DATEVALUE(REPLACE(REPLACE(RIGHT(A1,8),3,0,"/"),6,0,"/"))

 

[Ron Coderre]

With
A1: ABERDEEN12302009

This regular formula converts the last 8 characters into a date

B1: =--TEXT(--RIGHT(A1,8),"00\/00\/0000")

Format that cell as a date.

Is that something you can work with?

 

[Lucasv98]

Use Date() function.
Ex:
=DATE(RIGHT(A1,4),LEFT(RIGHT(A1,8),2),LEFT(RIGHT(A1,6),2))

Edit: Alternately use DateValue & Replace.
=DATEVALUE(REPLACE(REPLACE(RIGHT(A1,8),3,0,"/"),6,0,"/"))

worked like a charm, thanks

 

[GraH - Guido]

This regular formula converts the last 8 characters into a date
B1: =--TEXT(--RIGHT(A1,8),"00\/00\/0000")

Looked cool, so I gave it a try. FYI your formula formula seems not to be working if you have different local settings like myself. At my end it returns #VALUE!.

 

[Ron Coderre]

Looked cool, so I gave it a try. FYI your formula formula seems not to be working if you have different local settings like myself. At my end it returns #VALUE!.

Interesting....What are your local settings? I'm hoping this approach can be adapted for any region.

 

[Chihiro]

My second function and your setup works great if system/Excel's region is set to US (mm/dd/yyyy). But will not work well for British or Canadian set up, dd/mm/yyyy.

Date function is probably the best option to work in both environment. Other than setting up custom function in PowerQuery using M.

 

[bosco_yip]

This formula work for my computer of British date setting (dd/mm/yyyy)

A1 : ABERDEEN12302009

B1, formula :

=--TEXT(LEFT(RIGHT(A1&RIGHT(A1,8),12),8),"0000\/00\/00")

Regards
Bosco

 

[Chihiro]

Nice. Didn't think to make it into yyyy/mm/dd format. That's universal as far as I know across multiple systems.