En los Estados Unidos de América se utiliza un formato de fecha que antepone el mes al día y eso puede traernos algunos conflictos al intentar manipular los datos utilizando Excel en español ya que nuestro formato de fecha coloca primero el día y luego el mes. Hoy te mostraré tres técnicas que podrás utilizar para cambiar el formato de fecha Americano a Español.
Pero antes de adentrarnos en las técnicas antes mencionadas, debo aclarar que no siempre tendrás este problema al trabajar con este tipo de datos. Si recibes un archivo que tiene fechas en formato Americano, pero los datos han sido claramente identificados como fechas, Excel podrá convertir el formato con unos cuantos clics.
La siguiente imagen muestra en la columna A una serie de fechas que tienen el formato Americano, es decir mes, día y año.
Puedes notar que Excel ha identificado los datos como fecha por tres razones:
- Los datos de las celdas de la columna A están alineados a la derecha.
- El formato mostrado en la pestaña Inicio nos indica que las celdas tienen el formato Fecha.
- La barra de fórmulas muestra el valor de la celda en formato dd/mm/aaaa.
Ya que Excel ha identificado las fechas correctamente, solo necesitaremos cambiar el formato desplegado en pantalla. Para hacer dicho cambio debemos seleccionar las celdas, hacer clic derecho sobre ellas y elegir la opción Formato de celdas.
En la pestaña Número seleccionamos la opción Fecha y elegimos el formato que necesitamos. Al hacer clic en el botón Aceptar tendremos el formato de fecha deseado.
En estos casos, el problema se soluciona cambiando el formato de las celdas, pero habrá ocasiones en las que dicho cambio no funcionará y necesitaremos utilizar alguno de los métodos que explicaré a continuación.
Problema con el formato de fecha Americano
La razón principal con el formato de fecha Americano es cuando tenemos las fechas en formato texto y por lo tanto no es posible cambiar el formato de las celdas como lo hicimos anteriormente.
Este problema ocurre generalmente cuando se ha hecho una mala importación de datos a Excel o porque existe un sistema externo que genera automáticamente los datos y coloca las fechas como texto. La siguiente imagen muestra los datos de ejemplo cuando la columna de fechas tiene un formato de texto:
Puedes notar que los datos de la columna A están alineados a la izquierda, lo cual indica que Excel identifica los datos como cadenas de texto. Además, el formato desplegado en la pestaña Inicio se muestra como Texto y la barra de fórmulas muestra el mismo valor que la celda.
Una prueba que podemos hacer con estos datos es aplicar la función FECHANUMERO para descubrir cuáles valores pueden convertirse en formato fecha. Observa el resultado al aplicar la función a nuestros datos de ejemplo:
La función FECHANUMERO convierte una cadena de texto en una fecha en Excel, pero ya que nuestras fechas tienen el formato Americano, obtenemos el error #¡VALOR! porque la función espera que la fecha tenga el formato dd/mm/aaaa.
Aquellos valores que no muestran un error, son convertidos de manera errónea, tomando el primer valor del texto como el día de la fecha cuando en realidad se refiere al mes.
Es evidente que para convertir el formato de fecha Americano al formato en Español, será necesario manipular las cadenas de texto de las fechas para invertir el día y el mes. Para lograr este objetivo podrás utilizar cualquiera de los siguientes tres métodos.
Método 1: Fórmula para cambiar formato de fecha
El primer método sugerido es utilizar una fórmula para extraer cada parte de la cadena de texto para crear una nueva fecha con formato Español, es decir invirtiendo los valores de día y mes.
Tomaremos como referencia el carácter separador de día, mes y año de la fecha para obtener cada una de sus partes. En la gran mayoría de los casos dicho carácter será la diagonal (/), pero en caso de que no sea así, deberás utilizar el carácter adecuado.
En las fórmulas propuestas encontrarás frecuentemente la función ENCONTRAR que me ayudará a obtener la posición de la diagonal (/) dentro de la cadena de texto. Para encontrar la posición de la primera diagonal, utilizaré la siguiente fórmula:
=ENCONTRAR("/", A2)
Para encontrar la posición de la segunda aparición utilizaré la siguiente fórmula:
=ENCONTRAR("/", A2, ENCONTRAR("/", A2) + 1)
La segunda fórmula utiliza dos veces la función ENCONTRAR porque busca la posición de la segunda diagonal (/) a partir de la posición de la primera aparición. Quise explicar primero estas dos fórmulas porque las usaré constantemente en la solución.
La primera fórmula que necesitamos es para extraer la información del mes y para eso utilizaré la función IZQUIERDA de la siguiente manera:
=IZQUIERDA(A2, ENCONTRAR("/", A2) - 1)
A la posición de la primera diagonal (/) devuelta por la función ENCONTRAR le restamos uno y eso le indica a la función IZQUIERDA la cantidad de caracteres a extraer para obtener el mes. La siguiente imagen muestra el resultado al copiar esta fórmula para nuestros datos:
La parte más complicada de extraer de la cadena de texto será el día ya que se encuentra entre las dos diagonales (/) y para eso necesitaré utilizar la función EXTRAE indicando la cantidad exacta de caracteres que deseo extraer. La fórmula a utilizar es la siguiente:
=EXTRAE(A2, ENCONTRAR("/", A2) + 1, ENCONTRAR("/", A2, ENCONTRAR("/", A2) + 1) - ENCONTRAR("/", A2) - 1)
El segundo argumento de la función EXTRAE indica la posición de la primera diagonal (/) más uno. El tercer argumento indica la cantidad de caracteres a extraer y que es la resta de las posiciones de ambas diagonales. La siguiente imagen muestra el resultado:
Solo nos falta extraer el año y eso lo obtendremos utilizando la siguiente fórmula:
=DERECHA(A2, LARGO(A2) - ENCONTRAR("/", A2, ENCONTRAR("/", A2) + 1))
La cantidad de caracteres que obtendrá la función DERECHA está determinado por la longitud de la cadena de texto menos la posición de la segunda diagonal (/). Observa el resultado en la siguiente imagen:
Ahora que ya tenemos los tres elementos de cada fecha (mes, día y año), puedo utilizar la función FECHA para crear una fecha válida en Excel. Esta función me pide tres argumentos en el siguiente orden: año, mes y día.
Ya que en los datos de origen el año está indicado con 2 dígitos, me aseguro de sumarle 2 mil para obtener el año correcto.
Debes recordar que este método está integrado por dos pasos: El primero son las fórmulas que necesitamos para extraer el mes, día y año de la fecha con formato Americano. El segundo paso es la fórmula que toma cada una de esas partes para formar una fecha válida en Excel.
Método 2: Texto en columnas para cambiar formato de fecha
La diferencia de este método con el anterior es que evitaremos el uso de las fórmulas que extraen cada parte de la fecha y en su lugar utilizaremos el comando Texto en columnas. Pero antes de empezar necesitamos insertar las columnas en donde serán colocados los datos.
En seguida seleccionamos el rango que contiene las fechas en formato Americano y vamos a la pestaña Datos > Herramientas de datos > Texto en columnas.
Se iniciará el Asistente para convertir texto en columnas y en el paso 1 deberás seleccionar la opción Delimitados y pulsar Siguiente.
En el paso 2 del Asistente debemos indicar el carácter delimitador, que para nuestro ejemplo es la diagonal (/). Puedes observar en la siguiente imagen que he seleccionado la opción Otro y he ingresado el carácter correspondiente.
Además, la Vista previa de los datos ya muestra la manera en que Excel hará la división de la cadena de texto. Pulsamos el botón Siguiente para ir al último paso del Asistente donde deberás asegurarte de indicar la referencia de la celda donde se colocarán los datos.
Al pulsar el botón Finalizar se mostrará una advertencia para recordarnos que ya existen datos en la hoja. Pulsaré el botón Aceptar y obtendré la división de las cadenas de texto de la siguiente manera:
Tenemos el mismo resultado que con las fórmulas del ejemplo anterior y solo nos faltará crear la nueva fecha utilizando la función FECHA de la siguiente manera:
Recuerda que yo he sumado 2 mil al valor del año porque los datos de origen tenían solo dos dígitos. En caso de que tus datos tengan cuatro dígitos para el año, dicha suma no será necesaria.
Método 3: Texto en columnas versión abreviada
Si creías que el método anterior reducía el trabajo con fórmulas, el método que mostraré a continuación eliminará por completo la necesidad de fórmulas. Este método es similar al anterior pero solamente necesitaremos una columna adicional para depositar las nuevas fechas.
A continuación seleccionamos las celdas con las fechas en formato Americano y vamos a la pestaña Datos > Herramientas de datos > Texto en columnas. En el primer paso del Asistente seleccionamos la opción De ancho fijo.
El paso 2 del Asistente lo dejamos sin cambio alguno y pulsamos el botón Siguiente para ir directamente al paso 3 donde deberás hacer dos selecciones importantes.
En primer lugar deberás elegir el formato Fecha MDA que le indicará a Excel que la cadena de texto tiene el formato Mes, Día, Año (MDA). En seguida deberás elegir la celda destino en donde se colocarán las nuevas fechas. Al pulsar el botón Finalizar tendremos el resultado deseado como lo muestra la siguiente imagen:
Ahora conoces tres métodos para cambiar el formato de fecha Americano a Español en Excel. Si dicho cambio lo necesitas hacer con frecuencia, te recomiendo implementar las fórmulas en un archivo Excel que podrás utilizar como plantilla para reutilizarlo cada vez que lo necesites.
De lo contrario, si la conversión de fechas es ocasional, puedes evitar la construcción de las fórmulas utilizando el comando Texto en columnas con cualquiera de las dos técnicas que te he mostrado con dicho comando.
Descarga los libros de trabajo que he utilizado en la creación de este artículo y comprueba por ti mismo el funcionamiento de las fórmulas y métodos expuestos anteriormente.