jueves, 17 de diciembre de 2015

Formula to change Date (Text) to Date format

Example: "Tuesday, December 15, 2015" is in Text format. I need a formula, that can convert it to either a value or just a date format of "M/D/YYYY".
EDIT: Custom formatting or Formatting the cell does not resolve the issue Imgur Example

Assuming your date in Cell A1:
=DATE(RIGHT(RIGHT(SUBSTITUTE(A1,",",""),LEN(SUBSTITUTE(A1,",",""))-SEARCH(" ",SUBSTITUTE(A1,",",""))),4),MONTH(DATEVALUE(LEFT(LEFT(RIGHT(SUBSTITUTE(A1,",",""),LEN(SUBSTITUTE(A1,",",""))-SEARCH(" ",SUBSTITUTE(A1,",",""))),LEN(RIGHT(SUBSTITUTE(A1,",",""),LEN(SUBSTITUTE(A1,",",""))-SEARCH(" ",SUBSTITUTE(A1,",",""))))-5),SEARCH(" ",LEFT(RIGHT(SUBSTITUTE(A1,",",""),LEN(SUBSTITUTE(A1,",",""))-SEARCH(" ",SUBSTITUTE(A1,",",""))),LEN(RIGHT(SUBSTITUTE(A1,",",""),LEN(SUBSTITUTE(A1,",",""))-SEARCH(" ",SUBSTITUTE(A1,",",""))))-5)))&" 1")),RIGHT(LEFT(RIGHT(SUBSTITUTE(A1,",",""),LEN(SUBSTITUTE(A1,",",""))-SEARCH(" ",SUBSTITUTE(A1,",",""))),LEN(RIGHT(SUBSTITUTE(A1,",",""),LEN(SUBSTITUTE(A1,",",""))-SEARCH(" ",SUBSTITUTE(A1,",",""))))-5),2))
Result: imgur


REDDIT
https://www.reddit.com/r/excel/comments/3x8unq/formula_to_change_date_text_to_date_format/

No hay comentarios:

Publicar un comentario