sábado, 11 de julio de 2009

Cambiar Formato (Fecha) Americano a Español



"Todas las semanas me llega un informe con diversos datos y en la primera columna me encuentro con fechas en formato americano y, además, con formato de texto. Me gustaría poder convertirlas a formato español y poder operar con ellas".

Vamos a solucionar este problema con las funciones FECHA, IZQUIERDA, DERECHA y EXTRAE. Supongamos que tenemos la siguiente tabla con fechas, que además nos llegan con formato texto, en formato americano (mes,día,año) y queremos convertirlas en formato español y numérico (día,mes,año):

Vamos a "trocear" el problema para que se entienda mejor la solución:
1. Nos situamos en C3 y escribimos la fórmula:
=EXTRAE(B3;4;2)
La función EXTRAE nos permite extraer parte de un texto desde una posición inicial que le debemos indicar. En nuestro caso le estamos pidiendo que del texto que hay en B3 y desde la posición inicial 4 (4 caracteres) extraiga 2 caracteres. De esta manera obtendremos los dos caracteres referente al día.
2. Nos situamos en la celda D3 y escribimos la siguiente fórmula:
=IZQUIERDA(B3;2)
Esta función extrae dos caracteres de la celda B3 empezando por la izquierda.
3. Nos situamos en E3 y escribimos la fórmula:
=DERECHA(B3;4)
Esta función extrae cuatro caracteres de la celda B3 empezando por la derecha.

Ya hemos conseguido separar los caracteres referentes al día, mes y año:


Sólo nos queda "reunirlos" con formato de fecha válido para poder operar después con ellas. Esta labor la realiza la función FECHA:
4. Nos situamos en la celda F3 y escribimos:
=FECHA(E3;D3;C3)

Seleccionando ahora el rango C3:F3 y haciendo doble clic en la parte inferior derecha de la selección terminaríamos nuestro trabajo:

Podemos resumir todos estos pasos en una sola fórmula, a saber:
=FECHA(DERECHA(B3;4);IZQUIERDA(B3;2);EXTRAE(B3;4;2))


53 comentarios:

  1. excelente información es exactamente lo que estaba buscando!!!!!!

    ResponderEliminar
  2. Gracias Karen. Me alegra que te haya resultado útil.

    ResponderEliminar
  3. Hola Kiko, yo tengo un problema un tanto similar, en una hoja se descargaron datos de un programa en el que, no sé cómo; aparecen las fechas invertidas de la forma siguiente: 08/03/2009, en formato español, esto se leería como 8 de marzo de 2009, cuando en realidad la fecha es 3 de agosto de 2009. ¿Sabes cómo cambiarlo? No sé sime expliqué bien. Saludos y muchas gracias.

    ResponderEliminar
  4. Perdón pero no entiendo bien la pregunta. Aparentemente es lo mismo que solucionamos en este artículo. La cuarta fecha de este ejemplo aparece como 07/01/2009 cuando en realidad es 01/07/2009

    ResponderEliminar
  5. Hola Kiko,

    La fórmula funcionaria siempre y cuando la fecha americana no coincida con una fecha española. Por ejemplo 03/01/2010 siendo la fecha americana, usando tu fórmula se convierte en 20/06/2084 porque entiende que es 3 de Enero de 2010.

    ResponderEliminar
  6. Me ha gustado mucho la informacion, antes yo usaba mucho el comando "si", y se que funciona limitadamente, y con mucha mas cosas.Pero he visto muy funcional y hasta divertida la forma tambien de conseguir las fechas de esta forma. Gracias. Cristian

    ResponderEliminar
  7. Hola Kiko.Gracias por la información. Sólo hay una cosa que no me resulta: tras seleccionr el rango C3:F3 hago doble clik en la parte inferior derecha pero no sucede nada. Yo utilizo excel_Office 2007.

    El resto de fórmulas trabaja perfecto.Gracias

    ResponderEliminar
  8. Hola, oye la formula funciona muy bien con formato de texto, pero el ultimo ejemplo que pones 07/01/2009, en mi sistema si es coherente, no es un formato de texto, al ocupar la técnica no da como resultado 01/07/2009, que sugieres hacer con formatos númericos.

    ResponderEliminar
  9. Cuando hay celdas con formato texto y otras con formato numérico te sugiero la siguiente fórmula. La primera parte soluciona las celdas con formato texto y la segunda las que presentan formato numérico pero no devuelven la fecha en el formato deseado (español):
    =SI(ESERROR(MES(B4));FECHA(DERECHA(B4;4);IZQUIERDA(B4;2);EXTRAE(B4;4;2));
    FECHANUMERO(MES(B4)&"/"&DIA(B4)&"/"&AÑO(B4)))

    ResponderEliminar
  10. Hola Kiko,tengo un problema bastante complejo. Espero me puedas ayudar. Tengo un sistema contable que al sacar un reporte de facturación tira la fecha con el formato d/m/aa. El problema es que al exportarlo al excel,este automaticamente cambia el formato a m/d/aa. Es decir la fecha de una factura del 1ro de junio del 2010 en el sistema es 1/6/10 pero al pasarla a excel asoma el número tal cual en la celda (1/6/10) pero el formato se cambia a m/d/aa. Dicho de otra forma, pasa a ser enero 6 del 2010. No puedo usar la forma EXTRAE porque las fechas no tienen un formato de dos dígitos para cada valor. Es decir en los días y meses de un dígito solo sale 1 dígito y no 2. Ejemplo 1ro de junio de 2010 es 1/6/10 y no 01/06/10 y esto impide usar EXTRAE. Mi consulta puntual es: hay forma de decirle a excel quiero que cambies el formato de m/d/aa a d/m/aa pero no toques (des la vuelta) el numero que está en esa celda? Espero me puedas ayudar. Saludos. Pablo

    ResponderEliminar
  11. Hola Pablo. Por favor mándame una hoja a kiko.rial@ie.edu con el ejemplo de lo que te ocurre y lo que necesitas.

    ResponderEliminar
  12. Yo he encontrado una manera de hacerlo. Os la comento por si os sirve:

    1. cerramos excel
    2. nos vamos a panel de control--> configuración regional y elegimos Inglés de Estados Unidos.
    3. abrimos excel
    4. lo volvemos a cerrar.
    5. volvemos a panel de control y lo ponemos a español.
    6. excel habrá convertido todo en español sin fórmula.


    Espero que os sirva de ayuda.
    Saludos.

    ResponderEliminar
    Respuestas
    1. hahhaha... que feo....2013

      Eliminar
    2. gracias .. simple y sencillo y me quitaste la peor pesadilla que tenía en mis manos con un reporte que modificaba las fechas y debía recapturarlas manualmente

      Eliminar
  13. Muchas gracias, fue de gran ayuda la informacion.

    ResponderEliminar
  14. BUENA EXPLICACION PARA CAMBIAR DE FORMATO LAS FECHAS! GRACIAS FELIZ AÑO!!

    ResponderEliminar
  15. mi hemano un millon de gracias

    ResponderEliminar
  16. hola bueno el aporte pero una pregunta yo tengo un formato q también incluye la hora (02/25/11 01:55:00 PM) pero este esta en el formato americano lo que necesito es que se vuelva al español(25/02/11 01:55:00 PM)..la explicación q tu haces es muy buena pero yo necesito también la hora como puedo hacer??....de antemano muchas gracias

    ResponderEliminar
  17. Buena explicación pero a mi no me funciono, encontré otra solución, le cambias el formato de todas las fechas a numero sin decimales, lo guardas y después le pones otra vez el formato de fecha.

    ResponderEliminar
  18. Hola kiko, mi problema es el siguiente:
    tengo dos columnas en excel en formato general aaaa/mm/dd pero sin los separadores es decir aaaammdd y me interesaria que me restara la fecha menor de la mayor, pero claro como va año/mes/dia no la puedo pasar de formato general a fecha porque no lo entiende, y si resto las celdas lo único que hace es una resta numérica pero no de fechas. ¿Como soluciono el problema? Gracias de antemano. Pepe

    ResponderEliminar
    Respuestas
    1. Para que lo entienda como fecha debes escribir la siguiente fórmula (estoy suponiendo que la primera fecha la tienes escrita en la celda B2):
      =VALOR(DERECHA(B2;2)&"/"&EXTRAE(B2;5;2)&"/"&IZQUIERDA(B2;4))
      Con esto convertirás tus valores en fechas con las que trabajar.

      Eliminar
  19. como copio una columna que tiene solo fechas con formato de fechas a otra columna pero que se pase con formato de texto sin alterar la fecha

    ResponderEliminar
    Respuestas
    1. Partiendo de, por ejemplo, una fecha escrita en A1 nos ponemos en B1 y escribimos la siguiente fórmula:
      =TEXTO(A1;"dd/mm/aaaa")
      Ahora copiaríamos hacia abajo tantas celdas como tengamos y posteriormente procedemos a copiar y pegar valores.

      Eliminar
  20. Gracias por la repuesta me ha servido

    ResponderEliminar
  21. Hola KIko,
    gracias a tus comentarios he podido solucionar la mayor parte de mi problema
    ya que con las fechas en las que aparecen como dd/mm/aaaa todo bien, pero las que aparecen como dd/mmm/aaaa estas en texto americano no logro convertirlas a dd/mm/aaaa en español, ejemplo de lo que requiero 08/May/2012 a 08/05/2012 existira alguna forma de solucionarlo?

    ResponderEliminar
    Respuestas
    1. Perdón pero no sé si entiendo bien el problema. Supongo que la fecha de partida es May/08/2012 ? En este caso la fórmula a aplicar sería =IZQUIERDA(B3;3) De esta manera obtendríamos el mes. Si montas una tabla que sea el nombre del mes y en la siguiente columna el número del mes Ene;1;Feb;2... aplicando la función BUSCARV a dicha tabla obtendrías el número del mes y a partir de ahí ya sería el mismo caso que en este post.

      Eliminar
    2. Gracias por tu respuesta, esto funciona con los textos en español pero creo no haberme explicado los datos bajan como texto en ingles en el siguiente orden, 23/Aug/2012 se requiere cambiar a un formato en español 23/08/2012 espero exista alguna solucion.

      de nuevo muchas gracias.

      Eliminar
    3. Vamos allá, a ver si es esto lo que necesitas:
      * En A2, y en las celdas debajo de ésta, tenemos fechas en el formato que indicas. Por ejemplo: 23/aug/2012
      * En el rango D2:D13 escribo una lista con el nombre de los meses en inglés (sólo tres letras): Jan;Feb;Mar;Apr...
      *En el rango E2:E13 escribo el número correspondiente al mes, desde el 1 hasta el 12.
      En B2 escribo la siguiente fórmula:
      =FECHA(DERECHA(A2;4);BUSCARV(EXTRAE(A2;4;3);$D$2:$E$13;2;FALSO);IZQUIERDA(A2;2))
      Ya me dirás si es lo que buscabas.

      Eliminar
    4. excelente! esto lo soluciona, un millón de garcias

      Eliminar
  22. Si el mes o el día solo tienen un número no funciona la función. Por ejemplo 2/18/2013 Alguna solución??

    ResponderEliminar
    Respuestas
    1. Para que contemple cualquier caso, es decir, que tenga 1 ó 2 dígitos el día puedes utilizar la siguiente fórmula, suponiendo que la fecha en cuestión esté en la celda A1:
      =IZQUIERDA(A1;HALLAR("/";A1)-1)

      Eliminar
  23. Hola!! Se me hace muy util tus formulas, solo que tengo problemas al ejecutar la formula.. Y tengo el mismo problema de fechas pero no me sale :( podrias ayudarme por favor?

    ResponderEliminar
    Respuestas
    1. Necesito que me digas qué problema te encuentras. Un saludo

      Eliminar
  24. Claro... Problema ejemplo 02/20/2013 es mes, dia y año. Obviamente yo la quiero día mes y año aplico las formulas que das pero me marca error. Podrias ayudarme por favor...

    Saludos!!

    ResponderEliminar
    Respuestas
    1. El ejemplo que me mandas es igual al tratado en el post y, teóricamente, no debería darte problemas. Necesito ver el fichero para averiguar que ocurre. Puedes mandármelo a kiko.rial@gmail.com? Un saludo

      Eliminar
  25. Claro.. Ya te envié el correo. Gracias por tomarte un tiempo para ayudarnos...

    ResponderEliminar
  26. Muchas gracias por la solución, ya me salio... Oie tengo otra pregunta referente a fechas.. Podrias ayudarme?

    ResponderEliminar
  27. Tengo un problema.. tengo que sacar la diferencia entre dos fechas con horas por ejemplo:

    Ejemplo 1.

    Primera fecha + 1Hora Segunda fecha + 2Hora
    02/08/2011 18:00 pm 04/08/2011 20:00 pm

    de estas fechas quiero saber la diferencia tanto en dias y horas...

    Ejemplo 2

    Primera fecha + 1Hora Segunda fecha + 2Hora
    02/08/2011 18:00 pm 02/08/2011 20:00 pm

    En este ejemplo solo quiero conocer las horas transcurridas de ese mismo día...

    Ojala puedas ayudarme.. Gracias!!

    ResponderEliminar
    Respuestas
    1. Te recomiendo la lectura del artículo http://www.excelavanzado.com/2012/10/sumar-horas-en-excel.html del blog Excel Avanzado. Un saludo

      Eliminar
  28. ola tengo un problema mediante un formulario de vb en ecxel 2010 ingreso la fecha ej 12/02/2013 pero al pasarmela ala celda me la cambia por 02/12/2013 y la interpreta como 2 de dic del 2013 ,ya intente cambiando formatos de celda pero no funciona, habra algun codigo que al pasar ala celda me respete el formato dd/mm/yy....Saludos gracias

    ResponderEliminar
    Respuestas
    1. La solución debes aplicarla en la programación del formulario de VB y no en la celda.

      Eliminar
  29. Hola,
    tengo unos campos tipo fecha que me muestran el dia de la semana en español, hay alguna forma de hacer que se muestren en ingles?
    Por ejemplo:
    El valor del campo es "11/02/2013 10:00:00", que se muestra como "lun 11/02 10:00", yo quiero que me muestre "mon 11/02 10:00", como puedo hacerlo sin tener que utilizar funciones?
    Saludos y gracias

    ResponderEliminar
  30. Hola Kiko, soy Sebastian, la verdad que intente de varias formas de hacerlo y utilizando tus formulas, solucione el problema de una manera muy extraña, utilice parte de la formula de texto y aca viene lo extraño..., me dio parte del resultado, modificando los valores me quedo asi.
    =TEXT(C3;"mm/dd/e") o en español =texto(c3;"mm/dd/e), de esta forma una fecha que la tenia con estilo americano, se paso a español. Por favor proba y vas a ver que funciona.
    Eso si cuadno llegas a las fechas que poseen dias mayores a 10, ya no sirbe, pero como yo lo tengo ordenado por fecha, cambio la formula en ese punto, arrastro nuevamnete y listo!...

    ResponderEliminar
  31. Hola Kiko,
    llevo un tiempo buscando solución a un problema que parece ridiculo de sencillo pero sin resultado (y eso que he llamado al servicio de microsoft que me enviaron un enlace a un articulo de "How to"que no me sirvio para nada.
    En fin eso es lo que pasa: sin inserto una fecha usando el sistema europeo (por ejemplo pongo 24/5 para decir 24 de mayo) no me lo interpreta como fecha... almenos que la cifra de dia sea inferior a 12 y entonces me lo interpreta como mes.
    He intentado cambarlo desde los formatos de fecha (cambiando sistema, eligiendo formatos personalizados de dd-mm-yy) pero no hay manera: si no inserto la fecha en un formato americano (mm-dd) ya no lo considera como fecha. Tambien he entrado en las preferencias del sistema pero no he conseguido nada ahi tampoco.

    el verdadero problema no es como se vea la fecha una vez puesta sino que lo que pongo (en formato dd/mm)sea interpretado como fecha....

    agradezco tu ayuda

    Mounya

    ResponderEliminar
    Respuestas
    1. Hola Mounya. Pues la verdad es que no sé por qué no te funciona, ya que estoy probando en mi ordenador y escribo en mi hoja 25/5 y automáticamente me lo transforma a formato fecha 25-may (dd-mmm). Lo que parece claro es que en la configuración regional la tienes con el sistema americano y deberías cambiarla ¿tienes cargado el paquete de preferencias de idioma?

      Eliminar
  32. Pues la verdad es que no lo se... cuando voy a formato de fecha y ubicacion cambio la ubicacion a españa pero se ve que por defecto est Estados Unidos y no he encontrado en las preferencias de excel una manera de cambiar esto...

    ResponderEliminar
    Respuestas
    1. Hola otra vez! tenias razon, era la configuración de idioma de mi Mac, lo tenia en ingles y el formato de fecha de mi calendario se ajustaba a eso.Al cambiar "dates and time preference" a español y españa ya me solucionó el tema de escel :)
      muchas gracias!
      Mounya

      Eliminar
  33. Hola tengo un problema, tengo unos datos de un reporte que registra la fecha de esta forma:
    20/07/2014 9:09:56PM
    pero no la reconoce como fecha sino con un formato personalizado y yo necesito que excel la reconozca como fecha.. que puedo hacer?

    ResponderEliminar
    Respuestas
    1. Lo reconoce como formato personalizado porque no existe un formato de fecha "estándar" definido como dd/mm/aaaa h:mm:ss AM/PM
      Si te refieres a que lo reconozca como valor y no como texto entonces tienes que seleccionar las celdas donde tienes dichos registros y en formato de número personalizado escribir precisamente: dd/mm/aaaa h:mm:ss AM/PM

      Eliminar
  34. Buenas todos, estoy desesperada he probado casi todas las soluiones que dais pero la única que me funciona es: texto("mm,dd,aa") el problema como en el comentario que copio mas abajo, es cuando el día es mayor de 10 me deja de funcionar...por qué sucede eso???


    Hola Kiko, soy Sebastian, la verdad que intente de varias formas de hacerlo y utilizando tus formulas, solucione el problema de una manera muy extraña, utilice parte de la formula de texto y aca viene lo extraño..., me dio parte del resultado, modificando los valores me quedo asi.
    =TEXT(C3;"mm/dd/e") o en español =texto(c3;"mm/dd/e), de esta forma una fecha que la tenia con estilo americano, se paso a español. Por favor proba y vas a ver que funciona.
    Eso si cuadno llegas a las fechas que poseen dias mayores a 10, ya no sirbe, pero como yo lo tengo ordenado por fecha, cambio la formula en ese punto, arrastro nuevamnete y listo!...

    ResponderEliminar
  35. Muy bien, me ha funcionado a la perfección.

    ResponderEliminar