Cómo Solucionar Problema De Fecha Inversa En Excel

Cuando se trabaja con series temporales en Excel y se importan datos de fuentes que utilizan un formato de fecha diferente al de tu sistema operativo, Excel a veces carga las fechas de forma incorrecta. Puedes intercambiar el día y el mes o no reconocer la expresión como fecha y tratarla como texto. Cómo Solucionar Problema De Fecha Inversa En Excel 

La primera solución que debes probar es la función de Texto a Columnas - es rápida y fácil; a menudo funciona, pero a veces no. En estos casos, normalmente puedes arreglar las fechas utilizando fórmulas que combinan varias funciones de Excel de fecha y texto.

En este breve tutorial te mostraré estas dos soluciones.

Índice

    Cómo Solucionar Problema De Fecha Inversa En Excel (Ejemplo)

    Mi ordenador está configurado para trabajar con fechas en el formato "d/m/aaaa" (Reino Unido), pero a menudo trabajo con datos financieros históricos en el formato estadounidense "m/d/aaaa". Por ejemplo, puedo descargar los datos históricos del índice VIX del sitio web de la CBOE - la versión actual es un archivo CSV que empieza así:

    Date,VIX Open,VIX High,VIX Low,VIX Close
    1/2/2004,17.96,18.68,17.54,18.22
    1/5/2004,18.45,18.49,17.44,17.49
    1/6/2004,17.66,17.67,16.19,16.73
    1/7/2004,16.72,16.75,15.5,15.5

    Contiene la apertura, el máximo, el mínimo y el cierre diarios del índice VIX, a partir de enero de 2004.

    Cuando cargo este archivo en Excel, se ve así:

    Ejemplo del problema de la fecha inversa

    Mi ordenador, que busca fechas "d/m/aaaa", no tienes forma de saber que este archivo CSV en particular almacena las fechas en el formato "m/d/aaaa". Como resultado, el 2 de enero se convierte en el 1 de febrero, el 5 de enero se convierte en el 1 de mayo y así sucesivamente: el día y el mes se han invertido.

    Lee: ¿Cómo Insertar PDF En Un Documento De Word?

    Además, una vez que se llega al 13 de enero (fila 10), Excel ya no trata las expresiones como fechas, porque no hay ningún mes 13 o 14.

    En resumen, en la columna Fecha acabas con dos tipos de celdas:

    • Fechas incorrectas con el día y el mes invertidos (para las filas donde la fecha original tiene día <= 12)
    • Texto (para filas donde el día original, que Excel piensa que debería ser un mes > 12)

    Esto debe ser corregido antes de poder hacer cualquier trabajo significativo con los datos.

    Solución de Texto a Columnas

    Para aquellos que no están familiarizados con la función de Texto a Columnas, permítanme mencionarla primero, porque cuando funciona, es mucho más rápido que convertir las fechas usando algunas fórmulas que se construyen. Vale la pena probarlo primero.

    Selecciona la columna con fechas y ve al menú principal de Excel / Datos / Texto a Columnas.

    Solución de Texto a Columnas

    Aparecerá una ventana y pasarás por tres pasos. Los dos primeros pasos te permiten decidir si tus datos están delimitados y cuáles son los caracteres delimitadores - estos no son importantes cuando sólo tienes una columna seleccionada. Lo importante ocurre en el paso 3, donde puedes seleccionar el formato de los datos.

    seleccionar el formato de los datos

    Marca Fecha y selecciona el formato que creas que coincide con la fuente de datos original (por ejemplo, MDY si las fechas del archivo CSV original son algo así como "m/d/yyyy"). Haz clic en Finalizar y comprueba si las fechas se han corregido.

    Desafortunadamente, a veces esta solución simple no funciona y es el momento de la solución más difícil, basada en fórmulas, que presento a continuación.

    Solución manual basada en una fórmula

    Vamos a crear una fórmula para calcular las fechas correctas a partir de las incorrectas, de las cuales:

    • algunas son fechas con el día y el mes invertidos, por ejemplo el 1 de febrero, que debemos convertir en 2 de enero
    • algunas son texto no reconocido como fecha, por ejemplo, "13/1/2004" (texto), que debemos convertir en 13 de enero (fecha)

    Antes de empezar, guardemos la hoja de cálculo como un archivo Excel (xlsx o xls), que nos permitirá guardar fórmulas y formatos que un CSV no puede almacenar.

    Inserta una nueva columna antes de la columna B. En esta columna calcularemos las fechas correctas.

    Solución manual basada en una fórmula

    Cómo arreglar las celdas de día y mes invertidas

    El primer tipo de celdas (por ejemplo, en las filas 3-9 de nuestro ejemplo) se reconocen como fechas en Excel, sólo que con el orden inverso del día y el mes. Por lo tanto, podemos extraer el trozo de día, mes y año utilizando las funciones DÍA, MES y AÑO, respectivamente.

    Lo único que hay que recordar es que el resultado de DÍA es realmente el mes en nuestro caso y el resultado de MES es el día.

    A continuación, podemos combinar las piezas individuales en una fecha correcta, utilizando la función DATE de Excel, que toma tres argumentos: año, mes, día -en este orden- y devuelve una fecha.

    En la celda B3, calculemos la fecha correcta para la primera fila (que debería ser el 2 de enero de 2004, pero la celda A3 es el 1 de febrero de 2004).

    La fórmula en la celda B3 es:

    =DATE(YEAR(A3),DAY(A3),MONTH(A3))

    Cómo arreglar las celdas de día y mes invertidas

    ... donde:

    • Año(A3) es 2004
    • DÍA(A3) es 1, porque A3 es 1 de febrero
    • MONTH(A3) es 2

    ... por lo que los valores de nuestros argumentos DATE son:

    =DATE(2004,1,2)

    ... que devuelve la fecha correcta: 2 de enero de 2004.

    Fijación de las celdas de texto

    El otro tipo de celdas (por ejemplo, las filas 10 y siguientes de nuestro ejemplo) no se reconocen como fechas en Excel, porque el día (tratado como mes) es mayor que 12 y, por supuesto, no hay un 13º mes.

    Por ejemplo, en la celda A10 tenemos el texto "13/1/2004" que queremos convertir en la fecha 13 de enero de 2004.

    Lo haremos utilizando de nuevo la función FECHA, pero esta vez debemos obtener los tres argumentos -año, mes y día- de forma diferente. Debemos extraerlos como piezas del texto, utilizando las funciones de Excel IZQUIERDA y MEDIA.

    Obtención del mes con LEFT

    La función IZQUIERDA extrae el principio de una cadena desde el primer carácter hasta el n-ésimo carácter inclusive. Toma dos argumentos: el texto original y la posición del último carácter a incluir. Por lo tanto, si queremos obtener el mes de la celda A10 (obtener el "1" de "13/1/2004"), la fórmula es:

    =LEFT(A10,1)

    Significa: obtener el primer (un) carácter del texto de la celda A10.

    Lamentablemente, esto sólo funciona para los meses del 1 al 9. Para las fechas en las que el mes tiene dos dígitos (por ejemplo, el texto es "13/10/2004" = 13 de octubre de 2004), en realidad necesitamos la fórmula:

    =LEFT(A10,2)

    Por lo tanto, tenemos que hacer que el segundo argumento de la función IZQUIERDA sea dinámico.

    Podemos calcular el número de caracteres deseado (1 para los meses 1-9 o 2 para los meses 10-12) restando 8 a la longitud de toda la cadena (utilizando la función LEN de Excel).

    El resto de la cadena después del mes tiene siempre la misma longitud: 8 caracteres: "/dd/yyyy". El día siempre tiene dos caracteres, porque las fechas con el día 1-12 van todas al primer tipo de celdas que ya hemos fijado anteriormente.

    La fórmula dinámica para extraer el mes de la celda A10 es:

    =LEFT(A10,LEN(A10)-8)

    ... donde el resultado de LEN(A10)-8 es 1 para los meses de un solo dígito y 2 para los meses 10-12.

    Obtener el día y el año utilizando MID

    A diferencia del mes, el día y el año no pueden obtenerse usando la función LEFT, porque comienzan en el medio de la cadena. Utilizaremos la función de Excel MID, que toma tres argumentos:

    =MID(texto original, posición del primer carácter a devolver, número de caracteres)

    Por ejemplo, si queremos obtener el número 13 (el tercer y cuarto carácter) de la cadena "13/1/2004" en la celda A10, la fórmula es:

    =MID(A10,3,2)

    Sin embargo, debemos hacer que el segundo argumento (posición inicial) sea dinámico para que funcione tanto para los meses de un solo dígito como para los de dos.

    Podemos volver a utilizar la función LEN. El primer carácter del día puede calcularse como la longitud de la cadena menos 6. Los seis caracteres siguientes son siempre "d/aaaa", donde d es el segundo carácter del día.

    La fórmula para extraer el día de la celda A10 es:

    =MID(A10,LEN(A10)-6,2)

    ... donde LEN(A10)-6 es igual a 3 para los meses de un solo dígito y 4 para los meses 10-12.

    Podemos obtener el año de la misma manera, esta vez restando 3 a LEN, porque los caracteres restantes después del primer carácter del año son siempre "yyy":

    =MID(A10,LEN(A10)-3,4)

    El tercer argumento es ahora 4, porque year siempre tiene 4 caracteres.

    Obtención de la fecha

    El último paso para obtener la fecha correcta de las celdas de texto es combinar el día, el mes y el año utilizando la función FECHA. La fórmula en la celda B10 es:

    =DATE(MID(A10,LEN(A10)-3,4),LEFT(A10,LEN(A10)-8),MID(A10,LEN(A10)-6,2))

    donde:

    • MID(A10,LEN(A10)-3,4) es el año
    • LEFT(A10,LEN(A10)-8) es el mes
    • MID(A10,LEN(A10)-6,2)) es el día

    Obtención de la fecha

    Nota: En esta fórmula estamos calculando lo mismo (el LEN) tres veces. Si está trabajando con un conjunto de datos muy grande y el rendimiento es un problema, es posible que desees calcular el LEN en una columna diferente e incluir esa columna en esta fórmula. Para archivos pequeños, nuestra solución está bien.

    Combinando los dos tipos

    Hemos arreglado ambos tipos de celdas y ahora podemos combinar las fórmulas en una solución universal que funcione para todas las filas. Podemos utilizar la función IF:

    =IF(condición, resultado cuando es verdadero, resultado cuando es falso)

    Para comprobar el tipo de celda, utilizaremos la función ISNUMBER, que toma un argumento y devuelve TRUE si el argumento es un valor tratado como número en Excel, y FALSE en caso contrario.

    Todas las fechas son de hecho números en Excel, sólo que se muestran con un formato que las hace aparecer como fechas para los humanos. Intente cambiar el formato numérico de una fecha en Excel. Por ejemplo, encontrará que el 2 de enero de 2004 es en realidad el número 37.988. El número 1 es el 1 de enero de 1900.

    Construiremos nuestra función IF así:

    Si la celda de la columna A es del primer tipo (ISNUMBER devuelve TRUE), utiliza la fórmula del primer tipo, en caso contrario utiliza el segundo tipo.

    La fórmula combinada en la celda B3 es:

    =IF(ISNUMBER(A3),DATE(YEAR(A3),DAY(A3),MONTH(A3)),DATE(MID(A3,LEN(A3)-3,4),LEFT(A3,LEN(A3)-8),MID(A3,LEN(A3)-6,2)))

    ... introduciendo las fórmulas para ambos tipos que hemos desarrollado.

    Ahora puedes copiar esta fórmula en todas las filas de la columna B y pegarlas como valores. Deberías ver las fechas correctas.

    Es posible que quieras eliminar la columna original de fechas incorrectas si tu archivo es muy grande y el rendimiento es un problema, pero en cualquier caso te recomiendo que conserves también el archivo de datos original, por si descubres algún error en el futuro.

    Nuestra solución asume que el formato de fecha en el archivo CSV original, aunque no sea reconocido correctamente por nuestro ordenador, es correcto por sí mismo. A veces, lamentablemente, esto no es cierto.

    Es posible que tenga caracteres especiales junto a las fechas en algunas filas, como una estrella ("17/9/2015*" que denota un comentario o una situación especial en esa fecha). Esto podría ser un problema para nuestra función LEN.

    En algunos casos, el formato de la fecha es incoherente en todo el archivo (por ejemplo, el archivo comienza con "mm/dd/aaaa" pero de repente cambia a "m.d.a" a mitad de camino). Por supuesto, hay que comprobarlo antes de hacer cualquier trabajo.

    Conclusión

    En tutopremium.com esperamos que nuestra guía te pueda ayudar a ¿Cómo Solucionar Problema De Fecha Inversa En Excel?, haznos saber si quedaste con alguna duda o si te resulto efectivo el tutorial más abajo en los comentarios.

    TE PUEDE INTERESAR

    Deja un comentario

    Tu dirección de correo electrónico no será publicada.

    Go up